I would like to remove ALL data from the database (with auto-increment values). I use Room.

I have read in the documentation there is something like clearAllTables() but according to the documentation:

This does NOT reset the auto-increment value

So this method is not smart.

There is also no drop database.

To do that, I can remove file database from the device but this is not efficient.

Is there any way to remove all data from the database with auto-increment values?

It is important, because if I remove 10 rows, and after that I will add new 10 rows, these rows will have id starting from 10 not from 0.

CodePudding user response：

1. You could, prior to accessing the database delete the file(s), accessing the database will then create the database (probably not suitable to do at any time).

   • by files, if WAL (Write-Ahead Logging - the default) is in use then in addition to the file as per the 3rd parameter passed to the Room databaseBuilder. Two additional files may exist one suffixed with -wal, the other with -shm. These should also be deleted if they exist.

2. You could use clearAllTables and then get a SupportSQLiteDatabase and do an execSQL, for every user defined table using, or as below use a function (set just tested below) in an @Dao annotated abstract class or interface:-

   UPDATE sqlite_sequence SET seq = 0 WHERE name = 'the_name_of_the_table';

   • not 100% sure that Room let's you access sqlite_sequence, it didn't used to I believe, but may do now.

   • you could probably safely not have the WHERE clause, in which case every row (1 per table with AUTOINCREMENT see below re AUTOINCREMENT) will have the seq value set to 0.

     • Just done a test using @Query("UPDATE sqlite_sequence SET seq=0") fun resetSQLiteSequence() and it reset the seq to 0.

3. You could not use autogenerate = true and instead use @PrimaryKey val id: Long?=null (Kotlin) or @PrimaryKey Long id = null; (Java).

   • this will still generate a unique id (generally 1 greater then the last (no guarantee for whether using autogenerate = true or not)).
   • this is actually the recommended way (according to SQLite), it is more efficient as the sqlite_sequence table is not used to store the highest ever allocated rowid for a table. So there is no need to obtain the value nor update it when inserting.
     • rowid is a column that every Room table (bar a few) has. When a column is an integer type (Room gives a column type of INTEGER) and that column is the primary key then that column is an alias of the rowid column. see more at https://sqlite.org/autoinc.html
   • all that autogenerate = true does (SQLite wise) is add the AUTOINCREMENT keyword - again see https://sqlite.org/autoinc.html
   • autogenerate = true also alters the handling of values, if the value is 0 then the id will be generated otherwise not. With autogenerate = false, then null is when the id will be generated other values are taken as is.

Option 3 is the simplest solution to use and also probably the safest and most robust solution.

You commented:-

I use primary key for building relations inside this db. So I use it for unique identifying items. If you have a huge number of items in db, imagine how big ID will be after a hundred of cleans with keeping old ID

With SQLite the rowid is a 64bit signed integer, it can be as large as 9223372036854775807.

If that number is reached, then with autogenerate = true aka AUTOINCREMENT then an SQLITE FULL error will result. However, without then SQLite will try to find an unassigned id (highly likely for 2 reasons a) you are deleting rows and freeing id's and b) no device could probably store that much data (certainly not any android device)).