Can Android Room manage multiple databases and create databases from a template database?

Question

Any thoughts, suggestions, or ideas on best practice or option for the following?

Problem: With Android Room, what is the practical way to approach creating and handling multiple databases in a single Android app?

What I'm trying to do: I have an Android app intended to manage multiple research topics. The idea is an user can create databases specific to subjects or research and able to store sources, attachments and notes on those subjects. For example, an user can have a database specific to the history of modern music, a second database on the subject of the history of hunting, and even subjects as deep as micro-biological research.

My thoughts were to have separate databases vs. one database that stores all this data. Especially since attachments can be stored and take up space quite quickly. And these databases can be shared between the phone / tablet app and a desktop version. There is a Java desktop version of this being used.

What I've Done" I've really only searched here and googled some but appear kind-of vague. I'm familiar with and have migrated changes to a database but wasn't sure if this would always be the best way to create a new database, as well as renaming, etc.

This Android app comes with a predefined and pre-popuated database as a demonstration. This database hasn't changed for 2 years now. So, the idea was possibly having a "template.db" that could be used to create new databases and rename them accordingly.

Answer 1

With Android Room, what is the practical way to approach creating and handling multiple databases in a single Android app?

You can certainly handle multiple databases and multiple databases based upon the same schema.

The issue is how to ascertain what databases there are that can be used. If all the databases were located in the same path (or even multiple paths) then this could be used. Another methodology could be to have a database of the databases.

Here's an example that utilises the databases of databases (the "MasterDatabase") and allows access to x databases.

So first the MasterDatabase which has a simple table with an id column (could be dispensed with) and a column for the database name. The table (@Entity) being named MasterDatabaseList as per :-

@Entity(
        indices = { @Index(value = "databaseName", unique = true)
        }
)
class MasterDatabaseList {
    @PrimaryKey
    Long id;
    String databaseName;

    public MasterDatabaseList() {}

    @Ignore
    public MasterDatabaseList(String databaseName) {
        this.databaseName = databaseName;
    }

    public Long getId() {
        return id;
    }

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

    public String getDatabaseName() {
        return databaseName;
    }

    public void setDatabaseName(String databaseName) {
        this.databaseName = databaseName;
    }
}

• Note the unique index on the databaseName column

Accompanying the table is MasterDao an @Dao class :-

@Dao
abstract class MasterDao {
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    abstract long insert(MasterDatabaseList masterDatabaseList);
    @Query("SELECT * FROM masterdatabaselist")
    abstract List<MasterDatabaseList> getAllDatabases();
}

• allows rows to be inserted or extracted.
• a duplicated database will be ignored and thus not added.

MasterDatabase is the @Database class (which ties the previous classes to the database) and includes a method to get an instance of the database from which the MasterDao can be accessed :-

@Database(
        entities = {MasterDatabaseList.class},
        version = 1
)
abstract class MasterDatabase extends RoomDatabase {
    abstract MasterDao getMasterDao();

    static volatile MasterDatabase instance = null;

    public static MasterDatabase getInstance(Context context) {
        if (instance == null) {
            instance = Room.databaseBuilder(context,MasterDatabase.class,"master.db")
                    .allowMainThreadQueries()
                    .build();
        }
        return instance;
    }
}

Now the template database, Base???? (a simple single table database for the demo). First the table BaseTable @Entity class:-

@Entity
class BaseTable {
    @PrimaryKey
    Long id;
    String mydata;

    public BaseTable(){}

    @Ignore
    public BaseTable(String myData) {
        this.mydata = myData;
    }

    public Long getId() {
        return id;
    }

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

    public String getMydata() {
        return mydata;
    }

    public void setMydata(String mydata) {
        this.mydata = mydata;
    }
}

• a very simple table with an id column and a column that holds some string data.

Accompanying is the @Dao class BaseDao :-

@Dao
abstract class BaseDao {
    @Insert
    abstract long insert(BaseTable baseTable);
    @Query("SELECT * FROM basetable")
    abstract List<BaseTable> getAllBaseTables();
    @Update
    abstract int update(BaseTable baseTable);
}

• with very basic insert, extract and update

and as before the @Database class BaseDatabase :-

@Database(
        entities = {BaseTable.class},
        version = 1
)
abstract class BaseDatabase extends RoomDatabase {
    abstract BaseDao getBaseDao();

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

• note how the database name needs to be passed, that is basically the crux of catering for multiple databases.

With all of that a Demo Activity.

public class MainActivity extends AppCompatActivity {

    private static final String TAG = "DBINFO";

    MasterDatabase masterDB;
    MasterDao masterDao;

    /* 3 Lists that need to be synchronised index wise */
    /* i.e. each index position should hold the respective name/databaseobject/dao
    /* List of the known databases (their names) */
    List<MasterDatabaseList> masterDatabaseListList = null;
    /* List of the BaseDatabase objects */
    ArrayList<BaseDatabase> baseDatabaseList = new ArrayList<>();
    /* List of the BaseDao's */
    ArrayList<BaseDao> baseDaoList = new ArrayList<>();

    /* The current database */
    int currentBaseIndex = -1; /* Index into the three Lists */
    BaseDatabase currentDB = null;
    BaseDao currentDao = null;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        masterDB = MasterDatabase.getInstance(this);
        masterDao = masterDB.getMasterDao();
        masterDatabaseListList = masterDao.getAllDatabases();
        // Add default db1  if it does not exist
        if (masterDatabaseListList.size() < 1) {
            addBaseDB("db1");
        }
        buildBaseLists();

        /* Add some data to db1 IF it exists (it should) */
        setCurrentIndexDBandDao("db1");
        if (currentBaseIndex > -1) {
            currentDao.insert(new BaseTable("Blah for db1"));
        }

        /* Add some data to db2 (it will not exist) */
        /* noting that the database will be created if it does not exist */
        setCurrentIndexDBandDao("db2");
        if (currentBaseIndex == -1) {
            addBaseDB("db2");
        }
        if (currentBaseIndex > -1) {
            currentDao.insert(new BaseTable("Blah for db2"));
        }

        /* Extract and Log Data for ALL the BaseDatabase databases i.e. db1 and db2 */
        for(MasterDatabaseList masterdb: masterDao.getAllDatabases()) {
            Log.d(TAG,"Database is " + masterdb.getDatabaseName());
            setCurrentIndexDBandDao(masterdb.databaseName);
            if (currentBaseIndex > -1) {
                for(BaseTable bt: currentDao.getAllBaseTables()) {
                    Log.d(TAG,"Extracted Base Table  row where MyData is" + bt.getMydata());
                }
            }
        }
    }

    /* Add a new Database */
    /* Note that it assumes that it will now be the current */
    /* so the current values are set */
    private void addBaseDB(String baseDBName) {
        masterDao.insert(new MasterDatabaseList(baseDBName));
        buildBaseLists();
        setCurrentIndexDBandDao(baseDBName);
    }

    /* Build/ReBuild the 3 Lists according to the master database */
    /* This could be better managed so as to not rebuild existing database/dao objects */
    private void buildBaseLists() {
        int ix = 0;
        baseDatabaseList.clear();
        baseDaoList.clear();
        masterDatabaseListList = masterDao.getAllDatabases();
        // Loop through the databases defined in the master database adding the database and dao to the respective lists
        for (MasterDatabaseList masterDB: masterDao.getAllDatabases()) {
            BaseDatabase baseDB = BaseDatabase.getInstance(this, masterDB.getDatabaseName());
            baseDatabaseList.add(baseDB);
            baseDaoList.add(baseDB.getBaseDao());
            ix++;
        }
    }

    /* Set the current trio according to the database name that is:*/
    /*  1.the currentBaseIndex for the 3 Lists */
    /*  2. the BaseDatabase object */
    /*  3. the BaseDao */
    /* The index value (currentBaseIndex) is also returned */

    private int setCurrentIndexDBandDao(String baseDBName) {
        currentBaseIndex = getListIndexByBaseDBName(baseDBName);

        if (currentBaseIndex > -1) {
            currentDB = baseDatabaseList.get(currentBaseIndex);
            currentDao = baseDaoList.get(currentBaseIndex);
        }
        return currentBaseIndex;
    }

    /* Get the index according to the database name passed */
    /* note -1 signifies not know/found */
    private int getListIndexByBaseDBName(String baseDBName) {
        masterDatabaseListList = masterDao.getAllDatabases(); // OverKill????
        int rv = -1; // default to not found
        for(int i=0; i < masterDatabaseListList.size();i++) {
            if (masterDatabaseListList.get(i).databaseName.equals(baseDBName)) {
                rv = i;
                break;
            }
        }
        return rv;
    }

    /* Output all rows from the BaseTable for data extracted by the BaseDaos getAllBaseTables */
    private void logBaseData(List<BaseTable> baseTableList) {
        Log.d(TAG,"Current Database Index is " + currentBaseIndex + " DB name is " + masterDatabaseListList.get(currentBaseIndex).getDatabaseName());
        for(BaseTable bt: baseTableList) {
            Log.d(TAG,"\tMyData value is " + bt.getMydata());
        }
    }
}

Result

When the above is run for the first time the log includes:-

2021-09-16 11:39:30.262 D/DBINFO: Database is db1
2021-09-16 11:39:30.278 D/DBINFO: Extracted Base Table  row where MyData isBlah for db1
2021-09-16 11:39:30.278 D/DBINFO: Database is db2
2021-09-16 11:39:30.284 D/DBINFO: Extracted Base Table  row where MyData isBlah for db2

And via Android Studio's App Inspector the databases :-

and for the db2 BaseTable :-

• Note The above is only intended to cover the basics of utilising multiple databases in what is intended to be a simplistic explanation, as such the code has been kept short and simple. It would probably be unacceptable, as it is, for an App that would be distribtued.