How to create a new table in Room dynamically?

Question

Help me out please.
In my app user is able to create new database tables by pressing a button. User is also able to access this tables later to receive data from them.
Currently I utilize good old SQLite via dataBaseHelper class, so I could just

public static void createTable(String tableName) {
        databaseHelper.getWritableDatabase().execSQL("CREATE TABLE IF NOT EXISTS [" + tableName + "] (...columns)");
    }

and the job was done;

Now I want to use Room library in my App instead of DataBaseHelper.
Im looking at this DAO interface, which is strictly bound to particular predefined table, and dont know what to do.
Is there a way?

Answer 1

Is there a way?

There is BUT the dynamically added tables would probably have to be used via traditional (pre-room) methods via a SupportSQLiteDatabase instance (this being Room's equivalent of SQLiteDatabase).

So effectively you are defeating some of the core reasons to utilise Room, such as an Object Orientated approach and the reduction of Boiler-Plate code.

Example

The following simple example creates and populates (if new) a room generated/managed table and then dynamically creates and populates (if new) another table BUT outside of the OO side of room via a SupportSQLiteDatabase instance. Finally all data is extracted from the tables into a Cursor and the data is dumped (to prove the concept).

The App is run twice, to show that the existence of the non-room table does not result in room detecting a changed schema and the resultant exception.

• Note that the above does not take into consideration the management of a variable amount of dynamic tables such as storing/obtaining the table names of the dynamically added tables, this would further complicate matters.

The code is :-

BaseEntity.java

@Entity(tableName = "base")
public class BaseEntity {

    public static final String BASETABLE_NAME = "base";
    public static final String BASETABLE_COL_ID = BaseColumns._ID;
    public static final String BASETABLE_COL_VALUE = "value";
    public static final String BASETABLE_NAME_PLACEHOLDER = ":tablename:";
    public static final String BASETABLE_CREATE_SQL = "CREATE TABLE IF NOT EXISTS "
            + BASETABLE_NAME_PLACEHOLDER +
            "(" +
            BASETABLE_COL_ID + " INTEGER PRIMARY KEY," +
            BASETABLE_COL_VALUE + " TEXT)";
    @PrimaryKey
    @ColumnInfo(name = BASETABLE_COL_ID)
    Long id;
    @ColumnInfo(name = BASETABLE_COL_VALUE)
    String value;

    public BaseEntity() {}

    @Ignore
    public BaseEntity(String value) {
        this.value = value;
    }

    public Long getId() {
        return id;
    }

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

    public String getValue() {
        return value;
    }

    public void setValue(String value) {
        this.value = value;
    }

    @Ignore
    public static Long insertRow(SupportSQLiteDatabase sdb, String tableName, String value) {
        ContentValues cv = new ContentValues();
        cv.put(BASETABLE_COL_VALUE,value);
        return sdb.insert(tableName, OnConflictStrategy.IGNORE,cv);
    }

    @Ignore
    public static int getTableRowCount(SupportSQLiteDatabase sdb,String tableName) {
        int rv = 0;
        Cursor csr = sdb.query("SELECT count() FROM " + tableName,null);
        if (csr.moveToFirst()) {
            rv = csr.getInt(0);
        }
        csr.close();
        return rv;
    }
}

• As can be seen this is a mixture of Room code and non-room code

BaseEntityDao.java

@Dao
interface BaseEntityDao {

    @Insert
    long insertRow(BaseEntity baseEntity);

    @Query("INSERT INTO base (value) VALUES(:the_value)")
    void insertRow(String the_value);

    @Query("SELECT count() FROM base")
    Integer getRowCount();

}

• The room annotation processor requires the SQLite identifiers (table names, column names) to be as they are, they cannot be variables and hence these can only be used to access the Room defined table (hence the need for equivalents (in this example statically defined in the BaseEntity class)).

Database.java

@androidx.room.Database(version = 1,entities = {BaseEntity.class})
public abstract class Database extends RoomDatabase {

    public abstract BaseEntityDao baseEntityDao();
}

MainActivity.java

public class MainActivity extends AppCompatActivity {

    Database mDB;
    BaseEntityDao mDao;

    @Override
    protected void onCreate(@Nullable Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        mDB = Room.databaseBuilder(this,Database.class,"basedb")
                .allowMainThreadQueries()
                .build();
        mDao = mDB.baseEntityDao();
        addSomeDataViaRoom();
        String dynamicTableName = "testing";
        addTable(dynamicTableName);
        addSomeDataOutsideOfRoom(dynamicTableName);
        SupportSQLiteDatabase sdb = mDB.getOpenHelper().getWritableDatabase();
        Cursor csr = sdb.query("SELECT * FROM " + BaseEntity.BASETABLE_NAME);
        DatabaseUtils.dumpCursor(csr);
        csr = sdb.query("SELECT * FROM " + dynamicTableName);
        DatabaseUtils.dumpCursor(csr);
        mDB.close();
    }

    private boolean addTable(String tableName) {

        SupportSQLiteDatabase sdb = mDB.getOpenHelper().getWritableDatabase();
        try {
            sdb.execSQL(BaseEntity.BASETABLE_CREATE_SQL.replace(BaseEntity.BASETABLE_NAME_PLACEHOLDER, tableName));
        } catch (SQLiteException e) {
            return false;
        }
        return true;
    }

    private void addSomeDataViaRoom() {
        if (mDao.getRowCount() > 0) return;
        mDao.insertRow("A");
        mDao.insertRow("B");
        mDao.insertRow("C");
    }

    private void addSomeDataOutsideOfRoom(String tableName) {
        SupportSQLiteDatabase sdb = mDB.getOpenHelper().getWritableDatabase();
        if (BaseEntity.getTableRowCount(sdb,tableName) > 0) return;
        BaseEntity.insertRow(sdb,tableName,"X");
        BaseEntity.insertRow(sdb,tableName,"Y");
        BaseEntity.insertRow(sdb,tableName,"Z");
    }
}

Result (2nd run)

2019-10-26 08:04:28.650 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@5322d6
2019-10-26 08:04:28.651 I/System.out: 0 {
2019-10-26 08:04:28.651 I/System.out:    _id=1
2019-10-26 08:04:28.651 I/System.out:    value=A
2019-10-26 08:04:28.651 I/System.out: }
2019-10-26 08:04:28.651 I/System.out: 1 {
2019-10-26 08:04:28.651 I/System.out:    _id=2
2019-10-26 08:04:28.651 I/System.out:    value=B
2019-10-26 08:04:28.651 I/System.out: }
2019-10-26 08:04:28.651 I/System.out: 2 {
2019-10-26 08:04:28.651 I/System.out:    _id=3
2019-10-26 08:04:28.651 I/System.out:    value=C
2019-10-26 08:04:28.651 I/System.out: }
2019-10-26 08:04:28.651 I/System.out: <<<<<
2019-10-26 08:04:28.651 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@f873957
2019-10-26 08:04:28.652 I/System.out: 0 {
2019-10-26 08:04:28.652 I/System.out:    _id=1
2019-10-26 08:04:28.652 I/System.out:    value=X
2019-10-26 08:04:28.652 I/System.out: }
2019-10-26 08:04:28.652 I/System.out: 1 {
2019-10-26 08:04:28.652 I/System.out:    _id=2
2019-10-26 08:04:28.652 I/System.out:    value=Y
2019-10-26 08:04:28.652 I/System.out: }
2019-10-26 08:04:28.652 I/System.out: 2 {
2019-10-26 08:04:28.652 I/System.out:    _id=3
2019-10-26 08:04:28.652 I/System.out:    value=Z
2019-10-26 08:04:28.652 I/System.out: }
2019-10-26 08:04:28.652 I/System.out: <<<<<