SQLdelight drop all tables

Question

I'm using SQLDelight in the Kotlin/Native project (iOS & Android), and I'm looking for the best way to drop DB (all tables). User case: use log out from the app. So, I was looking for a way to erase data from all tables. So far I tried this, but I don't have access to sqlite_master table:

dropAllTables:
  PRAGMA writable_schema = 1;
  DELETE FROM sqlite_master WHERE type IN ('table', 'index', 'trigger');
  PRAGMA writable_schema = 0;
  VACUUM;
  PRAGMA INTEGRITY_CHECK;

Error: No table found with name sqlite_master

What is the best way to drop all table with SQLDeligth? (I want to avoid writing DROP TABLE fro each table)

Also, upsert { doesn't seems to work for me as well.. don't know how to provide label for it .

score 3 · Accepted Answer · answered Sep 06 '20 at 10:31

3

If you don’t care about anything in the dB and want it completely cleared you can just close the connection and delete the file, next time a connection is opened it will recreate the db

answered Sep 06 '20 at 10:31

Anstrong

734
3
7

In my use case, I would prefer to keep the connection and just remove data from tables. Is it possible? – ilbets Sep 09 '20 at 07:19
No nothing like that is supported in SQLDelight – Anstrong Sep 09 '20 at 12:33
1

I guess it would be great to give access to the `sqlite_master `. Anyway, thanks for your answer. – ilbets Sep 15 '20 at 10:15
@Anstrong I've tried to delete de db file but all queries afterwards fail with `no such table`. Do I have to create a new instance of `AndroidSqliteDriver` in order to trigger automatic recreation of the db? – Marco Romano Feb 23 '21 at 13:57
Room does have `clearAllTables()` it's a valid use case. Wonder why it's an accepted answer. – Happy Dev Nov 08 '21 at 04:02
1

How would I do this? I don't see anything in the APIs for deleting the database file. I could manually find the path on Android, but that doesn't help me on iOS. – Russell Stewart Feb 14 '22 at 16:17
`context.deleteDatabase()` – Anstrong Jun 25 '22 at 13:57

score 0 · Answer 2 · answered Oct 03 '20 at 12:16

Since I wanted to keep some immutable data in DB, the original solution will not work, so I did next:

keep a reference to the SqlDriver
Drop Required tables

val cursor = sqlDriver.executeQuery(
            null,
            "SELECT name FROM sqlite_master WHERE type = 'table' " +
                    //system tables
                    "AND name NOT LIKE '%sqlite%' " +
                    "AND name NOT LIKE '%metadata%' " +
                    "AND name NOT LIKE '%ios%' " +
                    "AND name NOT LIKE '%android%' " +
                    //immutable tables
                    "AND name != 'MySomeTalbe';",
            0
        )
        while (cursor.next()) {
            cursor.getString(0)?.let {
                sqlDriver.execute(null, "DELETE FROM $it", 0)
            }
        }

Note: it is Kotlin/Native project

SQLdelight drop all tables

2 Answers2