Search code examples
javaandroidandroid-room

How can I properly implement an extendable adjacency list database in Android Room?

I am currently trying to develop a time tracker app for Android. For that purpose, I want to store three things:

  • Projects
  • Tasks
  • Time Intervals

Since I want to support nested projects and tasks, I need to be able to represent both using some hierarchical SQL schema. For now, I have chosen to go with an adjacency list due to its simplicity. The Android docs recommend to use Android Room, which is what I am going with for now.

My current goal is to implement and test the data entity and DAO for an adjacency list once, such that I can simply reuse it for the project and task database entities.

I am unsure about how to properly define the most general adjacency list in Android Room. In particular, should the entities/DAOs be abstract? More importantly, how do I deal with the fact that the name of the table for project and task is different from the one I would use for the Adjacency List?

@Entity(foreignKeys = @ForeignKey(entity = AdjacencyList.class, parentColumns = "id", childColumns = "parent", onDelete = CASCADE, onUpdate = CASCADE, deferred = false))
public class AdjacencyList {
    @PrimaryKey
    public int id;
    public int parent;
}

@Dao
public interface AdjacencyListDao {
    // Queries will be coupled to the name adjacency_list
    // is there a way to generalize this?
    @Query("SELECT * FROM adjacency_list")
    List<User> getAll();
}
Solution

  • I believe that you will encounter issues with what you have.

    That is an AdjacencyList row parent column has to be a value, due to the Foreign Key, of an id that exists in the AdjacencyList.

    • when you specify @ForeignKey then you are defining an SQLite Foreign Key constraint(rule) that enforces referential integrity.

    When you say nested, what level of nesting?

    • For a single level of nesting then, taking a nested project, then a higher level project could have multiple nested projects but those nested projects could not have projects nested.

    • For a multi-level nesting than, a lower level project, could itself have lower level projects.

    • obviously the same for tasks, although at a guess tasks would be part of a project.

    I would suggest that you consider utilising what are termed as associative/mapping/reference (and many other descriptive names) table(s).

    Such a table has 2 core columns one that references the parent, the other that reference a child of the parent (the child being nested). The 2 columns are then used as the primary key thus catering for a multi-level nesting, albeit it requiring some form of recursion to move through the hierarchy.

    As an example, using raw/native SQLite, for which Room is a wrapper around, then perhaps consider the following SQL created and tested using an SQLite tool (Navicat for SQLite, there are others freely available) to demonstrate an associative/mapping/reference table that caters not only for multi-level nesting but also for a project to be nested in multiple projects (if so desired).

    DROP TABLE IF EXISTS project_hierarchy;
DROP TABLE IF EXISTS project;
CREATE TABLE IF NOT EXISTS project (
    id INTEGER PRIMARY KEY, 
    name TEXT, 
    etc TEXT
);

/* The mapping/associative/refernce .... table */
CREATE TABLE IF NOT EXISTS project_hierarchy (
    parent_project INTEGER REFERENCES project(id) ON DELETE CASCADE /* REFERENCES defines a FOREIGN KEY constraint(rule) saying the value MUST be an existing project id */, 
    child_project INTEGER REFERENCES project(id) ON UPDATE CASCADE, /* REFERENCES defines a FOREIGN KEY constraint(rule) saying the value MUST be an existing project id */
    PRIMARY KEY (parent_project, child_project)
);
/* note that the more conside column level foreign key definition has been used */
/* Room will build table level foreign key definitiions e.g. */
/* CREATE TABLE IF NOT EXISTS project_hierarchy */
/*     parent_project INTEGER, */
/*     child_project INTEGER, */
/* PRIMARY KEY (parent_project,child_project), */
/* FOREIGN KEY parent_project REFERENCES project(id) ON DELETE CASCADE ON UPDATE CASCADE, */
/* FOREIGN KEY child_project REFERENCES project(id) ON DELETE CASCADE ON UPDATE CASCADE */
/*;*/

/* Add some Projects (not any nestings at all)*/
INSERT INTO project VALUES
    (1,'P1','blah1') 
    ,(2,'P2','blah2')
    ,(3,'P3','blah3')
    ,(4,'P4','blah4')
    ,(5,'P5','blah5')
    ,(6,'P6','blah6')
    ,(7,'P7','blah7')
    ,(8,'P8','blah8')
    ,(9,'P9','blah9')
;

INSERT INTO project_hierarchy VALUES
    (1,2) /* Project 2 nested in project 1 */
    ,(1,3) /* Project 3 nested in project 1 as well */
    ,(4,5) /* Project 5 nested in project 4 */
    ,(2,6) /* project 6 is nested in project 2 which is nested in project 1 */
    ,(4,7) /* Project 7 is nested in project 4 as well as project 5 */
    ,(6,8) /* Project 8 is nested in project 6 which is nested in project 2 which is nested in project 1 */
    ,(4,6) /* Project 6 is additionally nested in Project 4 */ 
;

SELECT *
FROM project 
    /* More importantly, how do I deal with the fact that the name of the table for project and task is different from the one I would use for the Adjacency List? */
    /* JOIN the associative table to the project table where the parent_project is the same as the id in the project */
    JOIN project_hierarchy ON project_hierarchy.parent_project = project.id /* Joins the project_hierarchy table to the project table */
    /* JOIN the project table, for the nested projects, onto the associative table so the child_project is retrieved*/ 
    JOIN project AS nested /* aliased to circumvent ambiguous column names */ ON nested.id = project_hierarchy.child_project
;
/* Note as there is nothing to JOIN for Project 9 then in this select nothing is output */

/* Cleanup the demo environment */
DROP TABLE IF EXISTS project_hierarchy;
DROP TABLE IF EXISTS project;

    The result being:-

    enter image description here

    Using the above in Room

    Adapting the above for Room, would have two @Entity annotated classes:-

    Project:-

    @Entity
class Project {
    @PrimaryKey
    Long id=null;
    String name;
    String etc;

    Project(){}
    Project(String name, String etc) {
        this.name = name;
        this.etc = etc;
    }
    Project(Long id, String name, String etc) {
        this.id = id;
        this.name = name;
        this.etc = etc;
    }
}

    and Project_Hierarchy :-

    @Entity(
        primaryKeys = {"parent_project","child_project"},
        foreignKeys = {
                @ForeignKey(
                        entity = Project.class,
                        parentColumns = {"id"},
                        childColumns = {"parent_project"},
                        onDelete = ForeignKey.CASCADE,
                        onUpdate = ForeignKey.CASCADE
                ),
                @ForeignKey(
                        entity = Project.class,
                        parentColumns = {"id"},
                        childColumns = {"child_project"},
                        onDelete = ForeignKey.CASCADE,
                        onUpdate = ForeignKey.CASCADE
                )
        }
)
class Project_Hierarchy {
    long parent_project;
    @ColumnInfo(index = true)
    long child_project;

    Project_Hierarchy(){}
    Project_Hierarchy(long parent_project, long child_project) {
        this.parent_project = parent_project;
        this.child_project = child_project;
    }
}

    Although not exactly the same result wise in Room you would have a POJO for getting a Project with it's nested (1st Level) projects:-

    class ProjectWithFirstLevelNestedProjects {
   @Embedded
   Project project;
   @Relation(
           entity = Project.class,
           parentColumn = "id",
           entityColumn = "id",
           associateBy = @Junction(value = Project_Hierarchy.class,parentColumn = "parent_project", entityColumn = "child_project")
   )
   List<Project> nestedProject;
}
    • the associate by and Junction combination is how Room knows to do the join (actually it is an equivalent to a join that is used)

    Some methods in an @Dao annotated interface so they are implicitly abstract BUT cannot have bodies.

    An abstract class, which then requires the DAO methods to be abstract but have bodies can be used and as such is more flexible.

    @Dao
interface AllDAOs {
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    long insert(Project project);
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    long insert(Project_Hierarchy project_hierarchy);
    @Transaction
    @Query("SELECT * FROM project")
    List<ProjectWithFirstLevelNestedProjects> getAllProjectsWithFirstLevelProjects();
}
    • note the last method and it's apparent simplicity

    Putting it all together from a Room perspective is the @Database annotated class:-

    @Database(entities = {Project.class,Project_Hierarchy.class},exportSchema = false,version = 1)
abstract class TheDatabase extends RoomDatabase {

    abstract AllDAOs getAllDAOs();

    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 brevity and convenience .allowMainThreadQueries has been utilised, so the main thread can be used. It is not recommended to use the Main thread for an App this is to be distributed.

    Finally replicating the SQLite, noting that as we are dealing with Objects that the result will be different albeit that the underlying data in the tables will be the same. Some Activity code:-

    public class MainActivity extends AppCompatActivity {
    TheDatabase db;
    AllDAOs dao;

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

        long p1Id = dao.insert(new Project("P1","blah1"));
        long p2Id = dao.insert(new Project("P2","blah2"));
        long p3Id = dao.insert(new Project("P3","blah3"));
        long p4Id = dao.insert(new Project("P4","blah4"));
        long p5Id = dao.insert(new Project("P5","blah5"));
        long p6Id = dao.insert(new Project("P6","blah6"));
        long p7Id = dao.insert(new Project("P7","blah7"));
        long p8Id = dao.insert(new Project("P8","blah8"));
        long p9Id = dao.insert(new Project("P9","blah9"));

        dao.insert(new Project_Hierarchy(p1Id,p2Id));
        dao.insert(new Project_Hierarchy(p1Id,p3Id));
        dao.insert(new Project_Hierarchy(p4Id,p5Id));
        dao.insert(new Project_Hierarchy(p2Id,p6Id));
        dao.insert(new Project_Hierarchy(p4Id,p7Id));
        dao.insert(new Project_Hierarchy(p6Id,p8Id));
        dao.insert(new Project_Hierarchy(p4Id,p6Id));
        logProjectsWithFirstLevelNestings(dao.getAllProjectsWithFirstLevelProjects());
    }

    void logProjectsWithFirstLevelNestings(List<ProjectWithFirstLevelNestedProjects> nestings) {
        StringBuilder sb = new StringBuilder();
        for (ProjectWithFirstLevelNestedProjects pwflnp: nestings) {
            sb = new StringBuilder();
            for (Project project: pwflnp.nestedProject) {
                sb.append("\n\tNested Project Name is ").append(project.name).append(" ETC is ").append(project.etc).append(" ID is ").append(project.id);
            }
            Log.d("DBINFO",
                    "Project is " + pwflnp.project.name +
                            " ETC is " + pwflnp.project.etc +
                            " ID is " + pwflnp.project.id +
                            ". It has " + pwflnp.nestedProject.size() + " First Level Nested Projects; They are:-" +
                            sb
            );
        }
    }
}

    The Result

    The output to the Log is:-

    D/DBINFO: Project is P1 ETC is blah1 ID is 1. It has 2 First Level Nested Projects; They are:-
        Nested Project Name is P2 ETC is blah2 ID is 2
        Nested Project Name is P3 ETC is blah3 ID is 3
        
        
D/DBINFO: Project is P2 ETC is blah2 ID is 2. It has 1 First Level Nested Projects; They are:-
        Nested Project Name is P6 ETC is blah6 ID is 6
        
        
D/DBINFO: Project is P3 ETC is blah3 ID is 3. It has 0 First Level Nested Projects; They are:-
D/DBINFO: Project is P4 ETC is blah4 ID is 4. It has 3 First Level Nested Projects; They are:-
        Nested Project Name is P5 ETC is blah5 ID is 5
        Nested Project Name is P6 ETC is blah6 ID is 6
        Nested Project Name is P7 ETC is blah7 ID is 7
        
        
D/DBINFO: Project is P5 ETC is blah5 ID is 5. It has 0 First Level Nested Projects; They are:-
D/DBINFO: Project is P6 ETC is blah6 ID is 6. It has 1 First Level Nested Projects; They are:-
        Nested Project Name is P8 ETC is blah8 ID is 8
        
        
D/DBINFO: Project is P7 ETC is blah7 ID is 7. It has 0 First Level Nested Projects; They are:-
D/DBINFO: Project is P8 ETC is blah8 ID is 8. It has 0 First Level Nested Projects; They are:-
D/DBINFO: Project is P9 ETC is blah9 ID is 9. It has 0 First Level Nested Projects; They are:-

    This is clearly different to the original output. However the nestings are the equivalent and furthermore all Projects, nestings or not, have been output.