SQLite Forum

Reset database
Login

Reset database

(1) By anonymous on 2021-09-28 19:11:48 [link] [source]

Is there an SQL i.e. a programmatic way to reset a database to the same state as it was when it was created?

I can do this by deleting the file and recreating the database with sqlite3_open.

But I am pre-empting failure when I have just read access to the database; hence the question.

(2) By Larry Brasfield (larrybr) on 2021-09-28 19:32:44 in reply to 1 [link] [source]

I think your method for doing this should depend upon what "when it was created" means. If it means "before any tables (or table-like objects) and/or data were created, but after certain non-default DB-wide settings were established", then you can drop all the views, indices and tables, then do a SQL VACUUM. If things were done after the sqlite3_open call, such that the "created" state differs from the empty state all that dropping produces, then you will need to undo (where you can) the settings that are different. See the pragma list to peruse what those settings might be.

(3) By Warren Young (wyoung) on 2021-09-28 19:37:18 in reply to 1 [link] [source]

If you only have read access to the DB, how could you reset its state? The whole point of a DBMS like SQLite is that it presents access to the current transaction state of the DB. Rolling it back to a prior state is a transaction (or cancellation of all prior transactions) of its own, so it inherently implies write access.

I think you've got an XY problem here. Instead of telling us what the problem's solution should look like, tell us what problem you're actually trying to solve, which led you to believe SQLite should have a way to do what you're asking.

If you open a read-only database, and it causes this "failure" of yours, why is rolling back to a fresh new database the right behavior, especially given that there is explicit support for opening the database read-only?

(4) By anonymous on 2021-09-28 20:11:10 in reply to 3 [link] [source]

After

.open mydb.db

On checking the properties of mydb.db, its size is 0.

I am able to set the length of the file to 0 and can open or attach it using the sqlite3 APIs.

There is no DROP DATABASE or TRUNCATE DAATABASE. I did not want to simply drop the tables and views as I believe that will lead to fragmentation. I am not sure whether VACUUM locks the database.

Setting the file length to zero is faster than getting the list of objects and deleting each one and vacuuming the database; so, I'll settle for this ... unless there are side effects that I haven't thought of?

(5) By Larry Brasfield (larrybr) on 2021-09-28 20:20:49 in reply to 4 [link] [source]

After

.open mydb.db

On checking the properties of mydb.db, its size is 0.

That means very little considering that the shell, as a convenience for people who quit the CLI before actually doing anything to the named "database", defers its actual creation until something is done that would modify it.

I did not want to simply drop the tables and views as I believe that will lead to fragmentation. I am not sure whether VACUUM locks the database.

There can be no fragmentation without content interspersed among free pages. And VACUUM cures it anyway. And who cares if VACUUM locks the database? You would surely not want to "reset" it underneath other connections to it, so being locked would be good then. And if there are no other connections, it will not be locked.

Setting the file length to zero is faster than ...

Sorry, but that's just crazy. If you do manage to truncate the file to zero length, it will not be a SQLite database any more, not even an empty one.

I'm now fully with Warren on this being an XY problem. Please describe the problem you intend to solve rather than difficulties with solutions you have preconceived.

(6) By Warren Young (wyoung) on 2021-09-28 20:23:10 in reply to 4 [source]

its size is 0.

Opening an existing read-only database with data in it — implied by your use of the "reset" language — will not drop the database file to 0 size. Only opening a database in a writable directory will do that.

If that's all you want, then I don't see what you're looking for beyond

   unlink("mydb.db");
   sqlite3_open("mydb.db", &dbhandle);

There's your DROP DATABASE.

I am not sure whether VACUUM locks the database.

If only there were a way to be sure... (Hint: search for the word "lock" on that page.)

side effects that I haven't thought of

Calling truncate(2) on someone else's file is a great way to cause file corruption when another process tries to write into its still perfectly-good file handle.

At least with removing the old file and creating a new one, POSIX file semantics permit the old process to hang onto its doomed instance of the old file name until it closes it. Only once all the open FDs are closed will the file actually disappear from the filesystem.

But beware: if you're using WAL with this scheme, they'll fight over the SMH and WAL file names, again causing corruption.

Once again, I think you need to provide more detail about the actual use case instead of prescribing solutions ahead of knowledge.

(7) By Simon Slavin (slavin) on 2021-09-28 21:17:25 in reply to 1 [link] [source]

So you have read-only access to the database but you have read-write access to the drive the database is on. Why would an admin give you this combination of access ?

To get back to your question

  1. do you have sole access or is there a chance that some program not under your control has it open ?
  2. by 'the state when it was created' do you mean all tables are empty, or there are no tables ?

(8) By anonymous on 2021-09-28 21:28:19 in reply to 7 [link] [source]

I have read/write access; can't delete.

the state when it was created' = no tables no views, nothing i.e. size = 0

(9) By Ryan Smith (cuz) on 2021-09-28 23:09:49 in reply to 1 [link] [source]

Is there an SQL i.e. a programmatic way to reset a database to the same state as it was when it was created?

So many contradictions in one sentence, but since we're nice people, let's try answer them all after dissection:

  • Is there a Programmatic way? - Yes
  • Is there a SQL way? - No
  • when it was created? (read: before) - Yes (Unlink the file, reopen in SQLite)
  • when it was created? (read: after the FILE was created but nothing in the DB yet) - Yes, same as previous point.
  • when it was cretaed? (read: after, with content) - No, you have to truncate each table, or drop and recreate it, then redo whatever data entry was part of the create process, and even then you will have a different schema version etc. The only way to do this programmatically is painstakingly manual, in any Database engine).

I am pre-empting failure when I have just read access to the database

How can you change the "state" of anything in the DB, never mind the DB itself, to anything else (historic or otherwise) when you only have read-only access to it?
Further to that, what good is resetting an empty DB if you only have read-only access to it? It contains no information that would be useful to you and you certainly cannot add any.

I'm going to just assume you were intoxicated when posting and that you do have some write access to the file/directory.

If it's some standard setup DB, you could just keep a copy of the file around by another name, then once all connections to it are closed, replace it with the pre-made file and reopen. Be sure those connections are closed though, else everything will explode and everyone will die.

(10) By Keith Medcalf (kmedcalf) on 2021-09-29 01:04:46 in reply to 9 [link] [source]

It would perhaps be perspicacious to close (as in sqlite3_close) the database before deleting it. Just sayin.