1

I've been having this issue with how I should have my room DB for almost a week. I have 3 screens on my app - Home Feed, Group Feed, and Profile with user post feed all in chronological order. And a view post screen. The posts made in the group end up in the home feed. So all the responses for the posts on the 3 screens are the same.

And these 3 APIs are paginated on the app using paging 3 Remote mediator. Remote mediator wipes out this common post model on REFRESH.

                if (loadType == LoadType.REFRESH) {
                    database.feedRemoteKeysDao().clearRemoteKeys()
                    database.feedDao().clearFeed()
                }

When I move back to any of the other 2 screens I do not want to call the API again. How do I go about solving this?

-> I've thought of creating 3 different room tables for home, community, user. But then every time data changes (Ex: liked,new comment), I've to modify all 3 tables? What if the post exists only in 1 and not the other 2. Also my view post screen listens from the common post model. And updates UI based on changes in the local DB. Creating 3 different tables definitely seemed bad.

-> Not to wipe out the common post model on REFRESH only clear the remote keys table, I've done this I've observe for some reason my remote.nextKey often shows up as null and errors out.

-> Add another int field to know where the data came from

  • 0 for home
  • 1 for group
  • 2 for user profile

But here my post ID is the primary key, how would I go about having duplicate posts?

Any suggestions would be helpful!

Abhishek AN
  • 648
  • 7
  • 24

1 Answers1

0

But here my post ID is the primary key, how would I go about having duplicate posts?

You could use an int BUT use it so that it can flag all 1,2 or all 3 sources.

So 0 for no source (if even possible), 1 for home, 2 for group and 4 for user profile.

So 7 would indicate all 3, 6 user profile and group, 5 for user profile and home, 4 just user profile, 3 group and home, 2 just group and 1 for just home.

The alternative would be to have a table for the source and an intermediate table (called names such as associative,mapping,reference table) that has two core columns that form the primary key, one mapping the source, the other mapping the post.

By mapping a reference to unique column, typically the id column. This forms a many-many relationship so a source can have many (0-n) posts and a post can have many (0-n) sources.

This has the advantage that it can cope with billions of both sources and posts that is it can easily cater for new sources. Whilst the number of sources for the first (int method) is more limited.

here's a working example that demonstrates both.

First the Post (Entity) class which will be used by both methods:-

@Entity
class Post {
   /* For if Source is used as an int */
   static final int SOURCE_USERPROFILE = 4;
   static final int SOURCE_GROUP = 2;
   static final int SOURCE_HOME = 1;
   @PrimaryKey
   Long postId=null;
   String postData;
   int postSource=0; /* For if source is used as an int */

   Post(){}
   @Ignore
   Post(String postData, boolean sourceHome, boolean sourceGroup, boolean sourceUserProfile) {
      this.postData = postData;
      int source = 0;
      if (sourceHome) {
         source = source + Post.SOURCE_HOME;
      }
      if (sourceGroup) {
         source = source + Post.SOURCE_GROUP;
      }
      if (sourceUserProfile) {
         source = source + Post.SOURCE_USERPROFILE;
      }
      this.postSource = source;
   }
}

  • Note that for the 2nd (many-many relationship) that the following would not be needed:-

    • The three constants that represent the numerical value of the three sources.
    • the postSource member/field
    • the parameters in the @Ignored 2nd constructor

For the 2nd Method (many-many) the Source (Entity) class :-

@Entity
class Source {
    @PrimaryKey
    Long sourceId=null;
    String sourceName;

    Source(){}
    @Ignore
    Source(String sourceName) {
        this.sourceName = sourceName;
    }
}

Again for the 2nd Method the PostSourceMap (Entity) class :-

@Entity(
        primaryKeys = {"postIdMap","sourceIdMap"} /* composite primary key */

        /* Optional BUT ensures referential integrity */
        , foreignKeys = {
        @ForeignKey(
                entity = Post.class
                ,parentColumns = {"postId"}
                ,childColumns = {"postIdMap"}
                /* Optional BUT automatically maintains referential integrity */
                , onDelete = ForeignKey.CASCADE
                , onUpdate = ForeignKey.CASCADE
        ),
        @ForeignKey(
                entity = Source.class
                ,parentColumns = {"sourceId"}
                ,childColumns = {"sourceIdMap"}
                ,onDelete = ForeignKey.CASCADE
                ,onUpdate = ForeignKey.CASCADE
        )
}
)
class PostSourceMap {
   long postIdMap;
   @ColumnInfo(index = true)
   long sourceIdMap;
   PostSourceMap(){}
   @Ignore
   PostSourceMap(long postIdMap, long sourceIdMap) {
      this.postIdMap =postIdMap;
      this.sourceIdMap = sourceIdMap;
   }
}
  • As per the comment ForeignKey definitions aren't required, they do however enforce referential integrity.

For method 2 you would very likely to be able to retrieve a Post along with it's Sources, so a POJO class PostWithSources to facilitate this :-

class PostWithSources {
   @Embedded
   Post post;
   @Relation(
           entity = Source.class,
           parentColumn = "postId",
           entityColumn = "sourceId",
           associateBy = @Junction(
                   value = PostSourceMap.class,
                   parentColumn = "postIdMap",
                   entityColumn = "sourceIdMap"
           )
   )
   List<Source> sources;
}

To be able to insert and extract data an @Dao annotated abstract class (could be an interface) AllDao :-

@Dao
abstract class AllDao {
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    abstract long insert(Post post);
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    abstract long insert(Source source);
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    abstract long insert(PostSourceMap postSourceMap);


    @Query("SELECT * FROM post")
    abstract List<Post> getAllPosts();

    @Transaction
    @Query("SELECT * FROM post")
    abstract List<PostWithSources> getAllPostsWithSources();
}

To tie it altogether from a Room perspective, an @Dataabase annotated abstract class :-

@Database(entities = {Post.class, Source.class, PostSourceMap.class}, exportSchema = false, version = 1)
abstract class TheDatabase extends RoomDatabase {
    abstract AllDao getAllDao();

    private static volatile TheDatabase instance;
    static TheDatabase getInstance(Context context) {
        if (instance==null) {
            instance = Room.databaseBuilder(context,TheDatabase.class,"the_database.db")
                    .allowMainThreadQueries()
                    .build();
        }
        return instance;
    }
}
  • Note that for the first method it would be changed to use entities = {Post.class}. i.e. only the single table.

To put the above into action and insert some data then a basic activity MainActivity :-

public class MainActivity extends AppCompatActivity {

    TheDatabase db;
    AllDao dao;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        db = TheDatabase.getInstance(this);
        dao = db.getAllDao();

        long p1Id = dao.insert(new Post("POST DATA001",true,true,true));
        long p2Id = dao.insert(new Post("POST DATA002", false,true,true));
        long p3Id = dao.insert(new Post("POST DATA003", false,false,true));

        /* Add the Sources (easy to add more) */
        long s1Id = dao.insert(new Source("HOME"));
        long s2Id = dao.insert(new Source("GROUP"));
        long s3Id = dao.insert(new Source("USERPROFILE"));

        /* Map the posts with the sources */
        dao.insert(new PostSourceMap(p1Id,s1Id));
        dao.insert(new PostSourceMap(p1Id,s2Id));
        dao.insert(new PostSourceMap(p1Id,s3Id));
        dao.insert(new PostSourceMap(p2Id,s2Id));
        dao.insert(new PostSourceMap(p2Id,s3Id));
        dao.insert(new PostSourceMap(p3Id,s3Id));

    }
}

  • This inserts 3 posts using the equivalent sources for both methods:-

    • The first post has all three sources
    • The second post has Group and UserProfile as it's 2 sources.
    • The third post has just Userprofile as it's 1 and only source.

  • The above is designed to just be run the once.

When the above is run then the database consist of the following (as shown via App Inspection):-

Post table :-

enter image description here

  • For the first method, the other tables are irrelevant what matters, as far as sources is concerned is the postSource value.

  • For the second method the postSource value is redundant.

Source table :-

enter image description here

  • This is only relevant for the second method and contains a row for each source.

PostSourceMap table :-

enter image description here

  • Again this is only relevant for the second method.

  • The postIdMap is the postId of the respective Post

  • The sourceIdMap is the sourceId of the respective Source.

  • Together they tie a Post to a Source.

  • Typically the table is only used to determine which Post has which Sources or vice-versa.

  • as can be seen from examining the rows in the table:-

    • There 3 rows that have 1 in the PostIdMap column, so the Post with an id of 1 has 3 related Sources (i.e. all 3)
    • The are 2 rows that have 2 in the PostIdMap column, so the Post with an id of 2 has two related Sources (Source that has id 2 and Source that has id 3)
    • There is one rows that has 3 in the PostIdMap column, so the Post with an id of 3 has just the 1 Source (id 3).

  • You could view the data from the perspective of the Source. In which case it can be seen that

    • Source (id 3) has 3 related Posts,
    • Source (id 2) has 2 related Posts,
    • Source (id 1) has 1 related Post.
MikeT
  • 51,415
  • 16
  • 49
  • 68