Not able to update sqlite_sequence table using RoomDatabase.query

Question

We try to update sqlite_sequence with the following code.

WeNoteRoomDatabase weNoteRoomDatabase = WeNoteRoomDatabase.instance();
weNoteRoomDatabase.query(new SimpleSQLiteQuery("UPDATE sqlite_sequence SET seq = 0 WHERE name = 'attachment'"));

However, it has no effect at all. I exam the sqlite_sequence table content using SQLite browser. The counter is not reset to 0.

If we try to run the same query manually using SQLite browser on same SQLite file, it works just fine.

Our Room database is pretty straightforward.

@Database(
    entities = {Attachment.class},
    version = 6
)
public abstract class WeNoteRoomDatabase extends RoomDatabase {
    private volatile static WeNoteRoomDatabase INSTANCE;

    private static final String NAME = "wenote";

    public abstract AttachmentDao attachmentDao();

    public static WeNoteRoomDatabase instance() {
        if (INSTANCE == null) {
            synchronized (WeNoteRoomDatabase.class) {
                if (INSTANCE == null) {
                    INSTANCE = Room.databaseBuilder(
                        WeNoteApplication.instance(),
                        WeNoteRoomDatabase.class,
                        NAME
                    )
                        .build();
                }
            }
        }

        return INSTANCE;
    }
}

Any idea what we had missed out?

Additional information : clearing sqlite_sequence is not working in android room

What is the exact condition on name? does it when `name = attachment`? why you are using `name = \"attachment\"`? Why not `name = 'attachment'`? — yshahak, Dec 02 '18 at 07:14
`name = \"attachment\"` and `name = 'attachment'` makes no different. Both workable if I test it using Window SQLite browser. But, the problem is not lying on WHERE condition. If I execute `weNoteRoomDatabase.query(new SimpleSQLiteQuery("UPDATE sqlite_sequence SET seq = 0"));`, `sqlite_sequence` is still not being updated. It seems that Room has problem dealing with `sqlite_sequence` table. — Cheok Yan Cheng, Dec 02 '18 at 08:26
can you please do this on background thread. i am not sure about that but so far i just used background thread to update and write something in the room database — Shubham Mittal, Dec 07 '18 at 08:40
Yes, during testing, the query is executed in background thread. — Cheok Yan Cheng, Dec 07 '18 at 08:41
Why are you not using DAO classes? [Official docs](https://developer.android.com/reference/android/arch/persistence/room/RoomDatabase) also suggest it to use. — Pravin Divraniya, Dec 07 '18 at 12:05
sqlite_sequence is table created by room automatically, not by application. — Cheok Yan Cheng, Dec 07 '18 at 12:28
@CheokYanCheng `sqlite_sequence` is not created by `Room`, but by `SQLite`. — Martin Zeitler, Dec 13 '18 at 15:02

Martin Zeitler · Accepted Answer · 2018-12-14T16:24:33.457

2

Room doesn't use SQLiteDatabase - but it uses SupportSQLiteDatabase, while it's source code states, that it delegates all calls to an implementation of {@link SQLiteDatabase}... I could even dig further - but I'm convinced, that this is a consistency feature and not a bug.

SQLiteDatabase.execSQL() still works fine, but with SupportSQLiteDatabase.execSQL() the same UPDATE or DELETE queries against internal tables have no effect and do not throw errors.

my MaintenanceHelper is available on GitHub. it is important that one initially lets Room create the database - then one can manipulate the internal tables with SQLiteDatabase.execSQL(). while researching I've came across annotation @SkipQueryVerification, which could possibly permit UPDATE or DELETE on table sqlite_sequence; I've only managed to perform a SELECT with Dao... which in general all hints for the internal tables are being treated as read-only, from the perspective of the publicly available API; all manipulation attempts are being silently ignored.

edited Dec 14 '18 at 16:24

answered Dec 13 '18 at 14:45

Martin Zeitler

1
19
155
216

If you open up the sqlite file using SQLite browser, you can perform query `UPDATE sqlite_sequence SET seq = 0 WHERE name = 'attachment'` without issue. – Cheok Yan Cheng Dec 13 '18 at 15:53
@CheokYanCheng are there any records present? it also might be another driver (one which takes care of consistency and one which doesn't). table `sqlite_sequence` by itself has no keys - but the other tables. what you claim there only considers 50% of my answer. and `Room` is not a driver, it is an `ORM` abstraction layer, stacked upon the `SQLite` driver. – Martin Zeitler Dec 13 '18 at 16:01
I transfer the SQLite file from Android to Desktop, and use SQLite browser tool to test. That's the one and only one tool operates on it. What I'm trying bring up is that, updating the content of `sqlite_sequence` is a total legit operation - https://stackoverflow.com/questions/5586269/how-can-i-reset-a-autoincrement-sequence-number-in-sqlite . My question is why such updating operation doesn't work under Room – Cheok Yan Cheng Dec 13 '18 at 16:09
@CheokYanCheng if it were true what you claim, @Tjaart's answer should work - besides `SimpleSQLiteQuery` does nothing else. on the desktop the driver is `sqlite3.so` or `sqlite3.dll` - while Android uses it's own version of `sqlite3.so` abstracted by `SQLiteDatabase`, with `Room` `ORM` optionally stacked on top. that it works on the desktop does not matter, because with a server-less database, the driver acts as the "server". therefore it is imperative what the driver accepts, not what a whole other driver accepts, despite both being backed by the same `.db` file as physical storage facility. – Martin Zeitler Dec 13 '18 at 17:10
Although it looks like it should work, it doesn't if you do actual testing. – Cheok Yan Cheng Dec 13 '18 at 17:13
@CheokYanCheng testing on the desktop is pointless. try my approach on Android. – Martin Zeitler Dec 13 '18 at 17:13
No. We tested the Java code in Android (Doesn't work). We tested the SQL query using 3rd party tool in Desktop (Work, to confirm SQL query is correct). – Cheok Yan Cheng Dec 13 '18 at 17:15
@CheokYanCheng updated my answer with the results. still think that not `Room` is at fault (because it's just `ORM`), but the stated implementation of `SQLiteDatabase` which `SupportSQLiteDatabase` uses as it's delegate. further researching might be pointless, while the base `SDK` provides a workaround. – Martin Zeitler Dec 14 '18 at 16:32

Mayur Dabhi · Answer 2 · 2018-12-07T09:19:51.650

0

i think query is wrong, you should try below query

weNoteRoomDatabase.query(new SimpleSQLiteQuery("UPDATE sqlite_sequence SET seq = 0 WHERE name = attachment"));

edited Dec 07 '18 at 09:19

answered Dec 07 '18 at 08:44

Mayur Dabhi

3,607
2
14
25

down-voted, because the proposed syntax is wrong - and not the other. – Martin Zeitler Dec 14 '18 at 22:21

score 0 · Answer 3 · edited May 18 '20 at 02:53

0

Table sql_sequence is not managed by Room, so you need to edit it using a SupportSQLiteDatabase.

Try this:

String sqlQuery = "DELETE FROM sqlite_sequence WHERE name='attachment'";

weNoteRoomDatabase().getOpenHelper().getWritableDatabase().execSQL(sqlQuery);

edited May 18 '20 at 02:53

Long Ranger

5,888
8
43
72

answered Dec 07 '18 at 08:54

Tjaart

496
8
20

Have you tried this yourself? As, the proposed method still doesn't work for me, after testing. – Cheok Yan Cheng Dec 08 '18 at 03:16
It doesn't work for me either (tested with Room 2.0.0) – matteoh May 05 '19 at 19:38

score 0 · Answer 4 · answered May 17 '20 at 11:03

I'm using room database version 2.2.5

Here I'm unable to execute this query using Room Dao structure, so make one simple class and access method as like this and I got successful outcomes so this one is tested result. I'm using RxJava and RxAndroid for same.

public class SqlHelper {

    private static SqlHelper helper = null;

    public static SqlHelper getInstance() {
        if (helper == null) {
            helper = new SqlHelper();
        }
        return helper;
    }

    public Completable resetSequence(Context context) {
        return Completable.create(emitter -> {
            try {
                AppDatabase.getDatabase(context)
                        .getOpenHelper()
                        .getWritableDatabase()
                        .execSQL("DELETE FROM sqlite_sequence WHERE name='<YOUR_TABLE_NAME>'");
                emitter.onComplete();
            } catch (Exception e) {
                emitter.onError(e);
            }
        });
    }
}

Execute:

SqlHelper.getInstance()
         .resetQuizSequence(context)
         .subscribeOn(Schedulers.io()
         .observeOn(AndroidSchedulers.mainThread())
         .subscribe(() -> {}, error -> {});

score 0 · Answer 5 · answered Feb 15 '21 at 22:40

0

This works for me - Room 2.2.6

String sqlQuery = "DELETE FROM sqlite_sequence WHERE name='attachment'";

<YourDatabase>.getInstance(mContext).getOpenHelper().getWritableDatabase().execSQL(sqlQuery);

answered Feb 15 '21 at 22:40

GGK stands for Ukraine

516
6
17

Not able to update sqlite_sequence table using RoomDatabase.query

5 Answers5

Linked