I have a Room database in my application with one table containing received and sent messages. Inside of the table, the messages are just differentiated by the phone number, being null for the backend-server (since a server has no phone number) and the phone number of the user for the sent messages. (Entered on app installation, just as Whatsapp.) To sync the table with the backend, I introduced a new column, containing the backend id of the messages on the server. Since the server seperates sent and received messages (due to different information contained in the tables backend), the id of a sent message and the id of a received message can be equal, only distinguishable by the corresponding phone number. (Either null or own) So I created a unique constraint over both columns: backend_id & phone number.
@Entity(indices = [Index(value = ["backend_id", "senderNumber"], unique = true)])
data class Message(
var senderNumber: String?,
var message: String?,
var backend_id: String? = null,
var time : Date? = Date(),
var status : Status = Status.PENDING
) : ListItem(time), Serializable {
@PrimaryKey(autoGenerate = true) var id : Long? = null
}
But trying it out with some messages, I had to realize, that the database gladly accepts equal backend_ids, if the phone number is null. To make sure this was not an accident, I even wrote a UnitTest:
@RunWith(AndroidJUnit4::class)
class ExampleInstrumentedTest {
lateinit var db : MyDatabase
lateinit var dao : MessageDao
@Before
fun createDb() {
val context = ApplicationProvider.getApplicationContext<Context>()
db = Room.inMemoryDatabaseBuilder(
context, MyDatabase::class.java).build()
dao = db.messageDao()
}
@After
@Throws(IOException::class)
fun closeDb() {
db.close()
}
@Test(expected = Exception::class)
fun check_unique_constraint_is_violated() {
// Context of the app under test.
val message = Message(senderNumber = null, backend_id = "1", time = Date(), message = "Hello")
dao.insertAll(message)
dao.insertAll(message)
val allMessages = dao.getAll()
assertTrue(allMessages.size==2)
assertTrue(allMessages[0].backend_id==allMessages[1].backend_id)
}
}
This test fails, since it doesn´t throw any exception. Debugging it shows, that the Room database also doesn´t catch the exception silently, since both messages (being the same) are being inserted successfully, resulting in 2 messages.
So my question is: How can I ensure, that the result is unique over both columns, even if one of them is null? It seems a bit weird to me, that you can pass-by uniqueness, just by inserting null for one of the columns. It worked, when I only checked the backend_id in the index, throwing exceptions, when a sent and a received message had the same id. (But I obviously don´t want that.)
In case Database and Dao have any relevance to the solution: Database:
@Database(entities = [Message::class], version = 1)
@TypeConverters(Converters::class)
abstract class MyDatabase : RoomDatabase() {
override fun init(configuration: DatabaseConfiguration) {
super.init(configuration)
//Create and execute some trigger, limiting the entries on the latest 50
}
abstract fun messageDao() : MessageDao
companion object {
private var db: MyDatabase? = null
private fun create(context : Context) : MyDatabase {
return Room.databaseBuilder(context, MyDatabase::class.java, "dbname").build()
}
fun getDB(context : Context) : MyDatabase {
synchronized(this) {
if(db==null) {
db = create(context)
}
return db!!
}
}
}
}
MessageDao:
@Dao
interface MessageDao {
@Query("SELECT * FROM Message")
fun getAll() : List<Message>
@Insert
fun insertAll(vararg messages: Message) : List<Long>
}

