I have two tables - users and conversations. Many users may participate in one conversation and one user may be a member of many conversations
@Entity(tableName = "users")
public class User {
@NonNull
@PrimaryKey
@ColumnInfo(name = "userId")
private String id;
...
//Getters and setters
}
@Entity(tableName = "conversations")
public class Conversation {
@NonNull
@PrimaryKey
@ColumnInfo(name = "uuid")
private String id;
...
//Getters and setters
}
To track which user participate in which conversation I use third table:
@Entity(tableName = "user_conversation", indices = {@Index(value = {"userId", "conversationUuid"}, unique = true)},
primaryKeys = {"userId", "conversationUuid"},
foreignKeys = {
@ForeignKey(onDelete = CASCADE, entity = User.class, parentColumns = "userId", childColumns = "userId"),
@ForeignKey(onDelete = CASCADE, entity = Conversation.class, parentColumns = "uuid", childColumns = "conversationUuid")
})
public class JoinUserConversation {
@NonNull
private String userId;
@NonNull
private String conversationUuid;
...
//Getters and setters
}
Dao objects:
@Dao
public interface UsersDao {
@Insert(onConflict = OnConflictStrategy.REPLACE)
void insert(User user);
...
}
@Dao
public interface ConversationsDao {
@Insert(onConflict = OnConflictStrategy.REPLACE)
long insert(Conversation conversation);
....
}
When I delete a user or conversation from their tables I need to delete them from user_conversation too and I use onDelete = CASCADE for this purpose. But when I insert a user or conversation to their tables I do not need to delete them from user_conversation. But as I understand insert with @Insert(onConflict = OnConflictStrategy.REPLACE) will be performed by means of delete if inserting entity already exist in the table and corresponding user or conversation will be deleted from user_conversation table. How to keep user_conversation from changes on insert?
I believe that you could accomplish what you want by checking the id of the row to be inserted exists if so update if not then insert.
deferred = true along with @Transaction but there is no conflict due to the CASCADE doing what it is supposed to do.For example, for Users :-
Add
@Query("SELECT count() FROM users WHERE userId = :userId")
int getUserCount(String userId);
and
@Update()
int updateUser(User user);
And then have a method, such as :-
private void upsertUser(User u) {
if (mWTDao.getUserCount(u.getId()) > 0) {
mWTDao.updateUser(u);
} else {
mWTDao.insert(u);
}
}
Consider the following extract (from testing code):-
......... other code
upsertUser(new User("userid001","Fred"));
upsertUser(new User("userid002","Mary"));
upsertUser(new User("userid003","Jane"));
upsertUser(new User("userid004","Tom"));
mWTDao.insert( new Conversation("c001","SUBJECT A"));
mWTDao.insert( new Conversation("c002","SUBJECT X"));
mWTDao.insert(new JoinUserConversation("userid001","c001"));
mWTDao.insert(new JoinUserConversation("userid003","c001"));
mWTDao.insert(new JoinUserConversation("userid002","c002"));
mWTDao.insert(new JoinUserConversation("userid004","c002"));
logAllInfo("PRE");
upsertUser(new User("userid001","Harry"));
upsertUser(new User("userid002","Gertrude"));
logAllInfo("POST");
}
private void upsertUser(User u) {
if (mWTDao.getUserCount(u.getId()) > 0) {
mWTDao.updateUser(u);
} else {
mWTDao.insert(u);
}
}
private void logAllInfo(String tag) {
Log.d("INFO" + tag,"Logging Conversations via Original Mapping Table ");
for (JoinUserConversation juc: mWTDao.getAllJUC()) {
Conversation c = mWTDao.getConversation(juc.getConversationUuid());
User u = mWTDao.getUser(juc.getUserId());
Log.d("INFO" + tag,"\tFor Conversation " + c.getTitle() + "ID is (" + c.getId() + ") User is " + u.getName() + " ID is (" + u.getId() + ")");
}
Log.d("MAPINFO" + tag,"Logging Users ");
for (User u: mWTDao.getAllUsers()) {
Log.d("USERINFO" + tag,"\tID is " + u.getId() + " Name is " + u.getName());
}
Log.d("CONVINFO","Logging Conversations");
for (Conversation c: mWTDao.getAllConversations()) {
Log.d("CONVINFO" + tag,"\tID is " + c.getId() + " Ttitle is " + c.getTitle());
}
}
2019-09-18 08:14:19.214 D/INFOPRE: Logging Conversations via Original Mapping Table
2019-09-18 08:14:19.220 D/INFOPRE: For Conversation SUBJECT AID is (c001) User is Fred ID is (userid001)
2019-09-18 08:14:19.222 D/INFOPRE: For Conversation SUBJECT AID is (c001) User is Jane ID is (userid003)
2019-09-18 08:14:19.224 D/INFOPRE: For Conversation SUBJECT XID is (c002) User is Mary ID is (userid002)
2019-09-18 08:14:19.226 D/INFOPRE: For Conversation SUBJECT XID is (c002) User is Tom ID is (userid004)
2019-09-18 08:14:19.226 D/MAPINFOPRE: Logging Users
2019-09-18 08:14:19.227 D/USERINFOPRE: ID is userid001 Name is Fred
2019-09-18 08:14:19.227 D/USERINFOPRE: ID is userid002 Name is Mary
2019-09-18 08:14:19.227 D/USERINFOPRE: ID is userid003 Name is Jane
2019-09-18 08:14:19.227 D/USERINFOPRE: ID is userid004 Name is Tom
2019-09-18 08:14:19.227 D/CONVINFO: Logging Conversations
2019-09-18 08:14:19.228 D/CONVINFOPRE: ID is c001 Ttitle is SUBJECT A
2019-09-18 08:14:19.228 D/CONVINFOPRE: ID is c002 Ttitle is SUBJECT X
2019-09-18 08:14:19.233 D/INFOPOST: Logging Conversations via Original Mapping Table
2019-09-18 08:14:19.236 D/INFOPOST: For Conversation SUBJECT AID is (c001) User is Harry ID is (userid001)
2019-09-18 08:14:19.239 D/INFOPOST: For Conversation SUBJECT AID is (c001) User is Jane ID is (userid003)
2019-09-18 08:14:19.241 D/INFOPOST: For Conversation SUBJECT XID is (c002) User is Gertrude ID is (userid002)
2019-09-18 08:14:19.243 D/INFOPOST: For Conversation SUBJECT XID is (c002) User is Tom ID is (userid004)
2019-09-18 08:14:19.243 D/MAPINFOPOST: Logging Users
2019-09-18 08:14:19.244 D/USERINFOPOST: ID is userid001 Name is Harry
2019-09-18 08:14:19.244 D/USERINFOPOST: ID is userid002 Name is Gertrude
2019-09-18 08:14:19.244 D/USERINFOPOST: ID is userid003 Name is Jane
2019-09-18 08:14:19.244 D/USERINFOPOST: ID is userid004 Name is Tom
2019-09-18 08:14:19.244 D/CONVINFO: Logging Conversations
2019-09-18 08:14:19.245 D/CONVINFOPOST: ID is c001 Ttitle is SUBJECT A
2019-09-18 08:14:19.245 D/CONVINFOPOST: ID is c002 Ttitle is SUBJECT X