NestedTransactionTest.java

package io.github.fobo66.sqlite;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteStatement;
import android.util.Log;

import androidx.test.ext.junit.runners.AndroidJUnit4;
import androidx.test.platform.app.InstrumentationRegistry;

import org.junit.AfterClass;
import org.junit.Before;
import org.junit.BeforeClass;
import org.junit.Test;
import org.junit.runner.RunWith;

import static org.junit.Assert.*;

@RunWith(AndroidJUnit4.class)
public class NestedTransactionsTest {

    private Barcode barcode;

    private static SQLiteDatabase db;

    @BeforeClass
    public static void beforeAll() {
        String dbPath = InstrumentationRegistry.getTargetContext().getDatabasePath("sample.db").getAbsolutePath();
        db = SQLiteDatabase.openDatabase(dbPath, null, SQLiteDatabase.OPEN_READWRITE);
        db.beginTransaction();
        insertTempBarcode();
    }

    @AfterClass
    public static void afterAll() {
        db.endTransaction();
        db.close();
    }

    @Before
    public void setUp() {
        barcode = new Barcode();
        barcode.setBarcode("xyz");
    }

    @Test
    public void testGetCountShouldEqualOne() throws Exception {
        int count = getCount();
        assertEquals(1, count);
    }

    @Test
    public void testNestedTransactionRollbackIsPerformed() throws Exception {
        addSavepoint("test1");
        insertBarcode(barcode);
        rollbackToSavepoint("test1");
        int count = getCount();
        assertEquals(1, count);
    }

    @Test
    public void testRecordAddedInNestedTransactionWasRollbackedByID() throws Exception {
        addSavepoint("test2");
        long id = insertBarcode(barcode);
        System.out.println("testRecordAddedInNestedTransactionWasRollbackedByID: inserted record's id is " + id);
        rollbackToSavepoint("test2");
        assertNull(getBarcodeById(id));
    }

    private static void insertTempBarcode() {
        ContentValues values = new ContentValues();
        values.put("barcode", "abc");
        db.insertOrThrow("`BARCODE`", null, values);
    }

    private static long insertBarcode(BarCode barcode) {
        ContentValues values = new ContentValues();
        values.put("barcode", barcode.getBarcode());
        return db.insertOrThrow("`BARCODE`", null, values);
    }


    private int getCount() {
        Cursor cursor = db.rawQuery("select * from `BARCODE`", null);
        int count = cursor.getCount();
        cursor.close();
        return count;
    }
    /**
     * Semicolon before ROLLBACK does the trick,
     * because when the statement is compiled, framework looks at the 3 first letters
     * of the sql string to decide what to do {@see android.database.sqlite.SQLiteSession#executeSpecial},
     * and when it sees "ROL", it initiates ending of the transaction, it doesn't know that this ROLLBACK
     * is actually ROLLBACK TO.
     *
     * It's a kind of magic
     */
    private void rollbackToSavepoint(String savepointName) {
        db.execSQL(";ROLLBACK TO SAVEPOINT `"+ savepointName +"`;");
    }

    private void addSavepoint(String savepointName) {
        db.execSQL("SAVEPOINT " + savepointName + ";");
    }

    private BarCode getBarcodeById(long id) {
        Barcode barcode = null;
        Cursor cursor = db.rawQuery("select * from `BARCODE` where id = ?", new String[]{Long.toString(id)});

        if (cursor.moveToFirst()) {
            barcode = new Barcode();
            barcode.setId(cursor.getInt(cursor.getColumnIndex("id")));
            barcode.setBarcode(cursor.getString(cursor.getColumnIndex("barcode")));
        }

        cursor.close();
        return barCode;
    }
}