Does the fact I want to keep my db's seperate indicate a "smell"?
Yes, especially with Room. It introduces complexities and inefficiencies.
Is it possible to write a query where I can get the join model FooWithBar if the Foo table resides in a different database than the Bar table?
For your simple example Yes but as can be seen from the example not with an actual SQL JOIN (without attaching), that is you get the Foo object and mimic the JOIN by then getting the appropriate Bar (or Bars).
If you tried to mix Entities from different Databases then you will encounter problems e.g.
even though there are no issues adding (whilst editing) a Dao such as :-
@Query("SELECT * FROM foo JOIN bar ON foo.barId = bar.id")
fun getAllFooWithBar(): List<FooWithBar>
Which appears fine as per (screen shot from Android Studio) :-
When you compile you will get errors such as :-
E:\AndroidStudioApps\SO67981906KotlinRoomDate\app\build\tmp\kapt3\stubs\debug\a\a\so67981906kotlinroomdate\FooDao.java:22: error: There is a problem with the query: [SQLITE_ERROR] SQL error or missing database (no such table: bar)
public abstract void getAllFooWithBar();
^E:\AndroidStudioApps\SO67981906KotlinRoomDate\app\build\tmp\kapt3\stubs\debug\a\a\so67981906kotlinroomdate\FooDao.java:22: error: Not sure how to convert a Cursor to this method's return type (void).
public abstract void getAllFooWithBar();
To get anything that requires tables from both databases in a single query will be outside of the scope of Room as each database only knows about it's own tables.
However, if you ATTACH one database to another you then will have both database in one BUT Room does not understand it. So you basically have to revert to using a SupportSQLiteDatabase (similar to using native Android SQlite (but with some limitations)).
Example (very simple)
Foo Entity
@Entity
data class Foo(
@PrimaryKey
val fooId: Long?,
val barId: Long,
val fooText: String
)
FooDao
@Dao
interface FooDao {
@Insert
fun insert(foo: Foo): Long
@Query("SELECT * FROM foo")
fun getAllFoos(): List<Foo>
@Query("SELECT * FROM foo WHERE fooId=:fooId")
fun getFooById(fooId: Long): Foo
/* !!!!NO GO!!!!
@Query("SELECT * FROM foo JOIN bar ON foo.barId = bar.id")
fun getAllFooWithBar(): List<FooWithBar>
*/
}
FooDatabase
@Database(
entities = [Foo::class],
version = 1,
exportSchema = false
)
abstract class FooDatabase : RoomDatabase() {
abstract fun fooDao() : FooDao
fun attachBar(context: Context): Boolean {
var rv: Boolean = false
if (instance != null) {
val dbs = this.openHelper?.writableDatabase
val barpath = context.getDatabasePath(BarDatabase.DBNAME)
if (dbs != null) {
dbs.execSQL("ATTACH DATABASE '$barpath' AS $BAR_SCHEMA_NAME")
rv = true
}
}
return rv
}
fun closeInstance() {
if(instance == null) return
if (this.isOpen()) {
this.close()
}
instance = null
}
companion object {
@Volatile
private var instance: FooDatabase? = null
fun getInstanceWithForceOption(context: Context, forceReopen: Boolean = false): FooDatabase {
if (forceReopen) instance?.closeInstance()
if (instance == null) {
instance = Room.databaseBuilder(context,FooDatabase::class.java, DBNAME)
.allowMainThreadQueries()
.addCallback(FOO_CALLBACK)
.build()
}
return instance as FooDatabase
}
fun getInstance(context: Context): FooDatabase {
return getInstanceWithForceOption(context, false)
}
val FOO_CALLBACK = object: RoomDatabase.Callback() {
override fun onOpen(db: SupportSQLiteDatabase) {
super.onOpen(db)
}
override fun onCreate(db: SupportSQLiteDatabase) {
super.onCreate(db)
}
}
const val DBNAME: String = "foo.db"
const val BAR_SCHEMA_NAME = "bar_schema"
}
}
- CallBacks not used but ATTACH could be done in onOpen if ALWAYS access via attach
Bar Entity
@Entity
data class Bar(
@PrimaryKey
val id: Long?,
val barText: String
)
BarDao
@Dao
interface BarDao {
@Insert
fun insert(bar: Bar): Long
@Query("SELECT * FROM bar")
fun getAllBars(): List<Bar>
@Query("SELECT * FROM Bar WHERE id=:id")
fun getBarById(id: Long): Bar
}
BarDatabase
@Database(
entities = [Bar::class],
version = 1,
exportSchema = false
)
abstract class BarDatabase : RoomDatabase() {
abstract fun barDao() : BarDao
fun closeInstance() {
if (this.isOpen()) {
this.close()
}
instance = null
}
companion object {
@Volatile
private var instance: BarDatabase? = null
fun getInstanceWithForceOption(context: Context, forceReopen: Boolean = false): BarDatabase {
if (forceReopen) instance?.closeInstance()
if (instance == null) {
instance = Room.databaseBuilder(context,BarDatabase::class.java, DBNAME)
.allowMainThreadQueries()
.addCallback(BAR_CALLBACK)
.build()
}
return instance as BarDatabase
}
fun getInstance(context: Context): BarDatabase {
return getInstanceWithForceOption(context, false)
}
val BAR_CALLBACK = object: RoomDatabase.Callback() {
override fun onOpen(db: SupportSQLiteDatabase) {
super.onOpen(db)
}
override fun onCreate(db: SupportSQLiteDatabase) {
super.onCreate(db)
}
}
const val DBNAME: String = "bar.db"
}
}
- Again CallBacks do nothing
FooWithBar
class FooWithBar {
var foo: Foo
var bar: Bar
constructor(fooId: Long, fooDao: FooDao, barDao: BarDao) {
this.foo = fooDao.getFooById(fooId)
this.bar = barDao.getBarById(foo.barId)
}
}
- As you cannot get both a Foo and a Bar this does the equivalent of a join by getting the Foo via the FooDatabase and then gets the associated Bar via the BarDatabase.
MainActivity putting it altogether :-
class MainActivity : AppCompatActivity() {
lateinit var foodb: FooDatabase
lateinit var fooDao: FooDao
lateinit var bardb: BarDatabase
lateinit var barDao: BarDao
override fun onCreate(savedInstanceState: Bundle?) {
super.onCreate(savedInstanceState)
setContentView(R.layout.activity_main)
foodb = FooDatabase.getInstance(this)
fooDao = foodb.fooDao()
bardb = BarDatabase.getInstance(this)
barDao = bardb.barDao()
/* Add some data */
fooDao.insert(Foo(null,barDao.insert(Bar(null,"BAR1")),"FOO1"))
barDao.insert(Bar(null,"BAR UNUSED"))
fooDao.insert(Foo(null,barDao.insert(Bar(null,"BAR2")),"FOO2"))
/* Get equivalent of join (simple) using the FooWithBar */
val allFoosWithBars = mutableListOf<FooWithBar>()
for(foo: Foo in fooDao.getAllFoos()) {
allFoosWithBars.add(FooWithBar(foo.fooId!!,fooDao,barDao))
}
for(fwb: FooWithBar in allFoosWithBars) {
Log.d("FOOBARINFO","Foo is ${fwb.foo.fooText} Bar is ${fwb.bar.barText}")
}
//* Done with the Bar database Room wise
bardb.closeInstance()
foodb.attachBar(this) //<<<<< ATTACHES the Bar database to the Foo
/* Get a Supprort SQLite Database */
var sdb = foodb.openHelper.writableDatabase
/* Query Foo and the attached Bar */
var csr = sdb.query("SELECT * FROM foo JOIN ${FooDatabase.BAR_SCHEMA_NAME}.bar ON foo.barId = ${FooDatabase.BAR_SCHEMA_NAME}.bar.id")
DatabaseUtils.dumpCursor(csr)
csr.close()
}
}
Result
2021-06-16 16:35:04.045 D/FOOBARINFO: Foo is FOO1 Bar is BAR1
2021-06-16 16:35:04.045 D/FOOBARINFO: Foo is FOO2 Bar is BAR2
2021-06-16 16:35:04.092 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@ee9871b
2021-06-16 16:35:04.093 I/System.out: 0 {
2021-06-16 16:35:04.093 I/System.out: fooId=1
2021-06-16 16:35:04.093 I/System.out: barId=1
2021-06-16 16:35:04.093 I/System.out: fooText=FOO1
2021-06-16 16:35:04.093 I/System.out: id=1
2021-06-16 16:35:04.093 I/System.out: barText=BAR1
2021-06-16 16:35:04.093 I/System.out: }
2021-06-16 16:35:04.093 I/System.out: 1 {
2021-06-16 16:35:04.093 I/System.out: fooId=2
2021-06-16 16:35:04.093 I/System.out: barId=3
2021-06-16 16:35:04.093 I/System.out: fooText=FOO2
2021-06-16 16:35:04.093 I/System.out: id=3
2021-06-16 16:35:04.093 I/System.out: barText=BAR2
2021-06-16 16:35:04.094 I/System.out: }
2021-06-16 16:35:04.094 I/System.out: <<<<<
