Can Android Room validate an imported database before it is opened?

Solution 1:

I always thought Android Room validated the database at the moment of instance creation and build against the schema, but apparently not.

The database validation is part of the open process, which does not happen until you actually try to access the database, as opposed to when getting the instance.

I can't seem to get an imported database to fail in Android Room until first queried?

When you get the instance you can force an open by getting (or trying to get) a SupportSQLiteDatabase by using either getWritableDatabase or getReadableDatabase via the instance's openHelper.

e.g.

(Kotlin)

    db = TheDatabase.getInstance(this)
    try {
        val supportDB = db.openHelper.writableDatabase
    }
    catch(e: Exception) {
        ....
    }

(Java)

    db = TheDatabase.getInstance(this);
    try {
        SupportSQLiteDatabase supportDB = db.getOpenHelper().getWritableDatabase();
    }
    catch (Exception e) {
        ....
    }

Solution 2:

Alternative - Self validation

You could also do your own validation and thus avoid an exception (if the validation is simple enough). You could also make corrections thus allowing perhaps minor transgressions to be acceptable.

Before getting the actual instance, you could get a validation instance (different database name) that is created as per Room and then compare the schemas yourself.

here's an example designed to detect a table missing by creating the real database with a different table name (nottablex instead of tableX).

The TableX entity :-

@Entity
class TableX {
    @PrimaryKey
    Long id=null;
    String name;
    String other;
}

No Dao's as not needed for the example.

The TheDatabase with get instance methods, one for normal, the other for getting another (validation (empty model for schema comparison)) database but as an SQLiteDatabase.

@Database(entities = {TableX.class},version = 1)
abstract class TheDatabase extends RoomDatabase {

    private static volatile TheDatabase instance = null;
    private static volatile TheDatabase validationInstance = null;

    static TheDatabase getInstance(Context context, String databaseName) {
        if (instance == null ) {
            instance = Room.databaseBuilder(context,TheDatabase.class,databaseName)
                    .allowMainThreadQueries()
                    .build();
        }
        return instance;
    }

    static SQLiteDatabase getValidationInstance(Context context, String databaseName) {
        // Delete DB if it exists
        if (context.getDatabasePath(databaseName).exists()) {
            context.getDatabasePath(databaseName).delete();
        }
        // Create database and close it
        TheDatabase db = Room.databaseBuilder(context,TheDatabase.class,databaseName)
                .allowMainThreadQueries()
                .build();
        db.getOpenHelper().getWritableDatabase();
        db.close();
        return SQLiteDatabase.openDatabase(context.getDatabasePath(databaseName).getPath(),null,SQLiteDatabase.OPEN_READWRITE);
    }
}

• note that this forces the open to create the model/validation database (else the openDatabase would fail).

And finally for the demo MainActivity which creates an invalid database and then goes on to do a simple validation (just check that the expected tables exist). Only opening (never in the case of the example) the database if the tables expected by room exist.

public class MainActivity extends AppCompatActivity {

    public static final String DATABASE_NAME = "the_database.db";
    public static final String VALIDATION_DATABASE_NAME = DATABASE_NAME + "_validation";
    public static final String TAG = "DBVALIDATION";

    TheDatabase db;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        
        createIncorrectDatabase(this,DATABASE_NAME);
        if (validateDatabase(VALIDATION_DATABASE_NAME,DATABASE_NAME) < 0) {
            Log.d(TAG,"DATABASE " + DATABASE_NAME + " does mot match model.");
        } else {
            /* Database validated OK so use it */
            db = TheDatabase.getInstance(this,DATABASE_NAME);
        }
    }

    /* Purposefully create invalid database */
    private void createIncorrectDatabase(Context context, String databaseName) {
        File dbfile = context.getDatabasePath(databaseName);
        if (!dbfile.exists()) {
            dbfile.getParentFile().mkdirs();
        }
        SQLiteDatabase db = SQLiteDatabase.openOrCreateDatabase(context.getDatabasePath(databaseName),null);
        db.execSQL("CREATE TABLE IF NOT EXISTS nottablex(id INTEGER PRIMARY KEY,name TEXT)");
        db.close();
    }

    @SuppressLint("Range")
    private long validateDatabase(String modelDatabase, String actualDatabase) {
        String sqlite_master = "sqlite_master";
        /* Want to skip room_master_table and sqlite tables susch as sqlite_sequence */
        /* in this example only checking tables to show the basic technique */
        String wherecluase = "name NOT LIKE 'sqlite_%' AND name NOT LIKE 'room_%' AND type = 'table'";
        long rv = 0;
        /* Get the model/validation database */
        SQLiteDatabase modelDB = TheDatabase.getValidationInstance(this,modelDatabase);
        /* Only need to check if the database exists as otherwise it will be created according to Room */
        if (this.getDatabasePath(actualDatabase).exists()) {
            /* Open as an SQLiteDatabase so no Room open to throw an exception */
            SQLiteDatabase actualDB = SQLiteDatabase.openDatabase(this.getDatabasePath(actualDatabase).getAbsolutePath(),null,SQLiteDatabase.OPEN_READWRITE);
            /* Get the tables expected from the model Room database */
            Cursor modelTableNames = modelDB.query(sqlite_master,null,wherecluase,null,null,null,null);
            Cursor actualTableNames = null; /* prepare Cursor */
            /* Loop through the tables names in the model checking if they exist */
            while (modelTableNames.moveToNext()) {
                /* See if the expected  table exists */
                actualTableNames = actualDB.query(sqlite_master,null,"name=?",new String[]{modelTableNames.getString(modelTableNames.getColumnIndex("name"))},null,null,null);
                if (!actualTableNames.moveToFirst()) {
                    Log.d(TAG,"Table " + modelTableNames.getString(modelTableNames.getColumnIndex("name")) + " not found.");
                    rv = rv -1; /* Table not found so decrement rv to indicate number not found */
                }
            }
            /* Close the actualTableNames Cursor if it was used */
            if (actualTableNames != null) {
                actualTableNames.close();
            }
            /* Close the modelTableNames Cursor */
            modelTableNames.close();
            /* Close the actual database so Room can use it (comment out to show results in database Inspector)*/
            actualDB.close();
        } else {
            Log.d(TAG,"Actual Database " + actualDatabase + " does not exist. No validation required as it would be created");
        }
        /* Close and delete the model database (comment out to show results in database Inspector)*/
        modelDB.close();
        this.getDatabasePath(modelDatabase).delete();
        return rv;
    }
}

Result

The log includes :-

D/DBVALIDATION: Table TableX not found.
D/DBVALIDATION: DATABASE the_database.db does mot match model.

Bypassing the close and model delete the databases for the above are:-

• Note in this simple example Room would actually create the TableX table rather than fail with an exception.