Help with Slick/SQL


#1

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?