I am designing a CRUD API for “projects” that have read/write permissions. The API should look like:
case class Project(id: String,
…
)
create(p: Project, readOk: Seq[String], writeOK: Seq[String])
update(p: Project, readOk: Seq[String], writeOK: Seq[String])
delete…
getProjWithReadAccess…
getProjWithWriteAccess…
I want to use a normalized relational schema which means:
CREATE TABLE projects (
id VARCHAR(96) NOT NULL PRIMARY KEY,
…
);
CREATE TABLE permissions (
projectId VARCHAR(96) NOT NULL,
userId VARCHAR(254) NOT NULL,
readAllowed BOOLEAN,
writeAllowed BOOLEAN,
PRIMARY KEY (objectId, userOrGroupId)
);
I am using Play+Slick+mysql. Looking for a good way to write the query as SQL or in Slick DSL for the update action. It might involve updating some rows, inserting some and deleting some in permissions table.
I am assuming this is a common pattern when using REST APIs and a relational db backend.
If I need two queries (one per table), then I need a way to call both (via DAOs) from the controller. How would I do that?