Will there be any conflicts if Sqlite and Room both exist in same project with same db name in Android?

Question

I have few old modules which use SQlite Db and new modules use Room DB(both in the same project, SQlite is not yet migrated to Room), both use the same database name, will there be any conflicts?

for instance: Database name is "user_table" with SQLite I have 2 tables and new requirement uses room for new tables under same db name. will it cause any problem to db which is already created? If yes how will I tackle this?

Answer 1

If you are going to use the original tables in Room then you may have some issues as Room is quite specific about the table matching the respective Entity e.g. ONLY column types TEXT, INTEGER, REAL and BLOB can be used not null can be implied so must be used e.g. for a java primative (using Long rather than long etc can get around such issues, later versions of Room extend this requirement that tables match Entities with default values (I believe)).

If you are just using new tables for the Room side then non-room and room can co-exist. The issue then becomes the use of multiple connections. You may be able to circumvent this if you can use SupportSQLiteDatabase instead of SQLiteDatabase, and then only use the single connection (noting that SupportSQLiteDatabase is limited in comparison to SQLiteDatabase).

• e.g. SQLiteDatabase's query method builds the SQL via 7 or more parameters, whilst SupportSQliteDatabase's query takes the SQL as a sinlge parameter.

Simple Example

The following is a simple App that can switch between using just the pre-room table (mytable) and between using the additional room table (roomtable) in conjunction with mytable via the SupportSQliteDatabase.

First the DatabaseHelper that extends SQLiteOPenHelper (aka the pre-room) namely DatabaseHelperPreRoom.java

public class DatabaseHelperPreRoom extends SQLiteOpenHelper {
    public static final String DBNAME = "mydb";
    public static final int DBVERSION = 1;
    public static final String TABLE_MYTABLE = "mytable";
    public static final String COL_MYTABLE_ID = BaseColumns._ID;
    public static final String COl_MYTABLE_NAME = "name";

    SQLiteDatabase mDB;

    public DatabaseHelperPreRoom(Context context) {
        super(context,DBNAME, null, DBVERSION);
        mDB = this.getWritableDatabase();
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE_MYTABLE +
                "(" +
                COL_MYTABLE_ID + " INTEGER PRIMARY KEY," +
                COl_MYTABLE_NAME + " TEXT UNIQUE " +
                ")");

    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }

    public long insert(String name) {
        ContentValues cv = new ContentValues();
        cv.put(COl_MYTABLE_NAME,name);
        return mDB.insert(TABLE_MYTABLE,null,cv);
    }

    public Cursor getAll() {
        return mDB.query(TABLE_MYTABLE,null,null,null,null,null,null);
    }

    public static long insertPostRoom(SupportSQLiteDatabase db, String name) {
        ContentValues cv = new ContentValues();
        cv.put(COl_MYTABLE_NAME,name);
        return db.insert(TABLE_MYTABLE, OnConflictStrategy.IGNORE,cv);
    }

    public static Cursor getAllPostRoom(SupportSQLiteDatabase db) {
        return db.query("SELECT * FROM " + TABLE_MYTABLE);
    }
}

• Note the last two static methods added in preparation for Adding Room to the mix.

The Room Entity for the roomtable table (basically the same as mytable) RoomTable.java :-

@Entity(tableName = "roomtable")
public class RoomTable {

    @PrimaryKey
    @ColumnInfo(name = BaseColumns._ID)
    Long id;
    String name;

    public RoomTable () {}

    @Ignore
    public RoomTable(String name) {
        this.id = null;
        this.name = name;
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }
}

The Dao RoomTableDao.java

@Dao
public interface RoomTableDao {

    @Insert
    long insert(RoomTable roomTable);

    @Query("SELECT * FROM roomtable")
    List<RoomTable> getAll();

    @Query("SELECT count() AS " + BaseColumns._COUNT + " FROM roomtable")
    long getRowCount();
}

The Room Database RoomTableDatabase.java

@Database(version = DatabaseHelperPreRoom.DBVERSION, entities = {RoomTable.class})
public abstract class RoomTableDatabase extends RoomDatabase {

    abstract RoomTableDao roomTableDao();
}

Finally put it all together in an activity, using code that allows a simple switch between pre-room and room + pre-room MainActivity.java

public class MainActivity extends AppCompatActivity {

    public static final Boolean USINGROOM = true; //<<<<<<<<<< Switch control
    DatabaseHelperPreRoom mDBHlpr;
    RoomTableDatabase mRoomDB;
    RoomTableDao roomTableDao;
    SupportSQLiteDatabase mSDB;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        //<<<<<<<<<< The SWITCH as to use Pre-Room or ROOM
        if (!USINGROOM) {
            PreRoomCode();
        } else {
            PostRoomCode();
        }
    }

    private void PreRoomCode() {
        // Adds 3 rows if none exist and then log the data.
        mDBHlpr = new DatabaseHelperPreRoom(this);
        if (DatabaseUtils.queryNumEntries(mDBHlpr.getWritableDatabase(),DatabaseHelperPreRoom.TABLE_MYTABLE) < 1) {
            mDBHlpr.insert("A");
            mDBHlpr.insert("B");
            mDBHlpr.insert("C");
        }
        Cursor csr = mDBHlpr.getAll();
        while (csr.moveToNext()) {
            Log.d(
                    "PREROOMINFO",
                    "Name is " + csr.getString(csr.getColumnIndex(DatabaseHelperPreRoom.COl_MYTABLE_NAME)) +
                            " ID is " + csr.getString(csr.getColumnIndex(DatabaseHelperPreRoom.COL_MYTABLE_ID))
            );
        }
        csr.close();
    }


    private void PostRoomCode() {
        // Get around the fact that Room will only create it's tables for a new database
        createRoomTableIfNeeded();
        // Build the Room database
        mRoomDB = Room.databaseBuilder(this,RoomTableDatabase.class,DatabaseHelperPreRoom.DBNAME)
                .allowMainThreadQueries()
                .build();
        roomTableDao = mRoomDB.roomTableDao();
        // Get a SupportSQliteDatabase for use later
        mSDB = mRoomDB.getOpenHelper().getWritableDatabase();

        // If no rows then add 3 to new room table and another 3 to the pre-room table
        // i.e. basic proof of concept
        if (roomTableDao.getRowCount() < 1) {
            roomTableDao.insert(new RoomTable("X"));
            roomTableDao.insert(new RoomTable("Y"));
            roomTableDao.insert(new RoomTable("Z"));

            DatabaseHelperPreRoom.insertPostRoom(mSDB,"M");
            DatabaseHelperPreRoom.insertPostRoom(mSDB,"N");
            DatabaseHelperPreRoom.insertPostRoom(mSDB,"O");
        }
        // Show whats in the room table
        List<RoomTable> roomTableList = roomTableDao.getAll();
        for (RoomTable r: roomTableList) {
            Log.d("ROOMINFO","Name is " + r.getName() + " ID is " + r.getId());
        }
        // Show whats in the pre-room table
        Cursor csr = DatabaseHelperPreRoom.getAllPostRoom(mSDB);
        while (csr.moveToNext()) {
            Log.d(
                    "PREROOMINFO",
                    "Name is " + csr.getString(csr.getColumnIndex(DatabaseHelperPreRoom.COl_MYTABLE_NAME)) +
                            " ID is " + csr.getString(csr.getColumnIndex(DatabaseHelperPreRoom.COL_MYTABLE_ID))
            );
        }
    }


    private void createRoomTableIfNeeded() {
        /* As the Room table will not exist and will not get created as the db exists
            this is used to create the table.
         */
        SQLiteDatabase db = SQLiteDatabase.openDatabase(this.getDatabasePath(DatabaseHelperPreRoom.DBNAME).getPath(),null,SQLiteDatabase.OPEN_READWRITE);
        Cursor csr = db.query("sqlite_master",null,"name=?  AND type=?",new String[]{"roomtable","table"},null,null,null);
        int rowcount = csr.getCount();
        csr.close();
        if (rowcount == 0) {
            // CREATE SQL COPIED FROM RoomTableDatabase_Impl in java generated so the expected table is created
            db.execSQL("CREATE TABLE IF NOT EXISTS `roomtable` (`_id` INTEGER, `name` TEXT, PRIMARY KEY(`_id`))");
        }
        db.close();
    }
}

Results

USINGROOM = false;

2019-11-06 20:50:09.665 D/PREROOMINFO: Name is A ID is 1
2019-11-06 20:50:09.665 D/PREROOMINFO: Name is B ID is 2
2019-11-06 20:50:09.665 D/PREROOMINFO: Name is C ID is 3

• The result is the same irrespective of wether or not the roomtable exists, so switch to false at anytime yields the same result.

USINGROOM = true;

2019-11-06 20:52:44.042 D/ROOMINFO: Name is X ID is 1
2019-11-06 20:52:44.042 D/ROOMINFO: Name is Y ID is 2
2019-11-06 20:52:44.042 D/ROOMINFO: Name is Z ID is 3
2019-11-06 20:52:44.043 D/PREROOMINFO: Name is A ID is 1
2019-11-06 20:52:44.043 D/PREROOMINFO: Name is B ID is 2
2019-11-06 20:52:44.043 D/PREROOMINFO: Name is C ID is 3
2019-11-06 20:52:44.043 D/PREROOMINFO: Name is M ID is 4
2019-11-06 20:52:44.043 D/PREROOMINFO: Name is N ID is 5
2019-11-06 20:52:44.043 D/PREROOMINFO: Name is O ID is 6

• Again you can switch between Room and pre-room mode with no impact other than the results.