Relation between multiply databases

Question

It is easy to make relation between two tables of a database by @Relation and @ForeignKey of the Room library

And in SQLite we can join tables from different databases

But how can I do it by Room Library?

Answer 1

In Room you will not be able to code cross database foreign keys. The same restriction applies to SQLite. However, a Foreign Key is not required for a relationship to exist, it is a constraint(rule) used to enforce the integrity of a relationship.

Likewise in Room you will not be able to utilise cross database relationships. The @Relation annotation basically defines join criteria used the for queries that Room generates.

However, you can programmatically have relations between two room databases via the objects.

Example As a basic example (based upon a Room database I was looking at) consider:-

The first database (already existed), whose abstract class is Database which has a single entity that is defined in the Login class and has all the Dao's in the interface AllDao.

A Login object having 4 members/fields/columns, the important one being a byte[] with the hash of the user, named userHashed.

The second database, whose abstract class is OtherDatabase which has a single entity defined in the UserLog class and has all the Dao's in the interface OtherDBDao.

A UserLog object having 3 members/fields/columns, the importane/related column being the hash of the respective user(Login) (the parent in the Login table).

With the above consider the following :-

    //First Database
    db = Room.databaseBuilder(this,Database.class,"mydb")
            .allowMainThreadQueries()
            .build();
    allDao = db.allDao();

    //Other Database
    otherdb = Room.databaseBuilder(this,OtherDatabase.class,"myotherdb")
            .allowMainThreadQueries()
            .build();
    otherDBDao = otherdb.otherDBDao();

    // Add some user rows to first db
    Login l1 = new Login(t1,t2,t3,10);
    Login l2 = new Login(t2,t3,t4,20);
    Login l3 = new Login(t3,t4,t1,30);
    Login l4 = new Login(t4,t1,t2,40);
    allDao.insertLogin(l1);
    allDao.insertLogin(l2);
    allDao.insertLogin(l3);
    allDao.insertLogin(l4);


    // Get one of the Login objects (2nd inserted)
    Login[] extractedLogins = allDao.getLoginsByUserHash(t2);
    // Based upon the first Login retrieved (only 1 will be)
    // add some userlog rows to the other database according to the relationship
    if (extractedLogins.length > 0) {
        for (int i = 0;i < 10; i++) {
            Log.d("USERLOG_INSRT","Inserting UserLog Entry");
            otherDBDao.insertUserLog(new UserLog(extractedLogins[0].getUserHashed()));
        }
    }

    UserLog[] extractedUserLogs = otherDBDao.getUserLogs(extractedLogins[0].getUserHashed());
    for(UserLog ul : extractedUserLogs ) {
        // ....
    }

The above :-

1. builds both databases.
2. Adds 4 users to the first database.
3. extracts all of the Login objects that match a specific user (there will only be 1) from the first database.
4. for each Login extracted (again just the 1) it adds 10 UserLog rows to the other database.
5. as the TEST, uses the userhash from the first database to extract all the related UserLog rows from the other database.
6. to simplify showing the results a breakpoint was place on the loop that would process the extracted UserLog objects.

Of course such a design would probably never be used.

The following is a screen shot of the debug screen when the breakpoint is triggered :-