2. Quick User guide

Step 1 : Create your projects

• You must first create a separate Scala compilation unit for your database project. The Slick-macros database project provides a good start.
• Add the Slick-macros dependency to your database project
• Create another project for your main application. Your main application (the one that access the database) should be located in a separate project.

Step 2 : Define your database schema

This is where Slick-macros comes to help. Simply define your case classes inside a Scala object and prefix it with the @Modelannotation. The example below will create a three table database schema from the three case classes below.

@Model object XDb {
  object UserRights extends Enumeration {
    type UserRights = Value
    val ADMIN = Value(1)
    val GUEST = Value(2)
  }
  import UserRights._

  case class Company(name: String, website: String)

  @Part case class Address(num: Int, road: String, zip: String)

  case class Member(login: String, 
                    rights: UserRights, 
                    addr: Address, 
                    company: Company, 
                    manager: Option[Member])

  case class Project(name: String, company: Company, members: List[Member])
}

The code above will be processed by applying the following rules :

• A type mapper is generated for each Enumeration, so that they can be used as table columns (only Int values are handled for now)
• an attribute that references another case class is converted to a foreign key
• an attribute that references a collection of objects of another case class triggers the creation of a assoc table (many2many relationship)
• a Slick table object (including the forInsert method) and a Slick query object are generated for each case class

Enumeration Mapping

The UserRights Enumeration below

  object UserRights extends Enumeration {
    type UserRights = Value;
    val ADMIN = Value(1); // only Value(Int) supported here
    val GUEST = Value(2)
  }
  import UserRights._

is augmented with a type mapper so that it can be transparently persisted/loaded in/from the database

  implicit val UserRightsTypeMapper = MappedColumnType.base[UserRights.Value, Int](
                                            { it  => it.id },
                                            { id => UserRights(id) })

Case class Mapping

The company case class below

  case class Company(name: String, website: String)

is augmented with :

• the Company class because it does not define a primary key (see @PK annotation below) will have an autoincremented id field added

• the xid function definition is added for direct access to the company id primary key.

• The CompanyTable class is created to hold the database mapping info.

• a default companyQuery Object is also created.

The AST for the code below is generated for the Company case class

  case class Company(id: Option[Long], name: String, website: String, large: Array[Byte]) {
    def xid = id.getOrElse(throw new Exception("Object has no id yet"))
  }

  class CompanyTable(tag: Tag) extends Table[Company](tag, "company") {
    def id = column[Long]("id", O.PrimaryKey, O.AutoInc)
    def name = column[String]("name")
    def website = column[String]("website")
    def large = column[Array[Byte]]("large")

    def * = (id.?, name, website, large).shaped <> (
        { case (id, name, website, large) => Company(id, name, website, large) },
        { x: Company => Some((x.id, x.name, x.website, x.large)) })
      
    def forInsert = (name ~ website ~ large).shaped <> (
        { t => Company(None, t._1, t._2, t._3) }, 
        { (c: Company) => Some((c.name, c.website, c.large)) })
  }

  val companyQuery = TableQuery[CompanyTable]
  type CompanyCrud = CrudEx[Company, CompanyTable]

Part mapping for tables with more than 22 columns

To handle rows with more than 22 columns, Slick requires to divide case classes into parts. To define a case class as a part of another case class, simply prefix it with the @Partannotation.

  @Part case class Address(num: Int, road: String, zip: String)

No special handling is done on the case class itself but whenever it is referenced in another case class (the whole one), the table mapping will include it.

Foreign key and many to one relationship mapping

When the case class contains a reference to another mapped case class, it is converted to a foreign key. Thus the code for the Member case class below

  case class Member(
             login: String, 
             rights: UserRights, 
             addr: Address, 
             company: Company, 
             manager: Option[Member])

will generate the following slick table mapping with the application of the following rules :

• The MemberTable class and the memberQuery object are created
• Since the addr field references a "part", the Address is embedded in the Member table
• The company field references a case class, it is thus converted to a foreign key that references the Company entity.
• Since the manager field optionnaly references a Member object, it is thus converted to a nullable foreign key

  case class Member(id: Option[Long], 
                    login: String, 
                    rights: UserRights, 
                    addr: Address, 
                    companyId: Long, 
                    managerId: Option[Long]) 
{
    def xid = id.getOrElse(throw new Exception("Object has no id yet"))
    def company(implicit session: JdbcBackend#SessionDef) = companyQuery.where(_.id === companyId).first; 
    def manager(implicit session: JdbcBackend#SessionDef) = memberQuery.where(_.id === managerId).firstOption
}

  class MemberTable(tag: Tag) extends Table[Member](tag, "member") {
    def id = column[Long]("id", O.PrimaryKey, O.AutoInc);
    def * = (id.?, login, rights, (num, road, zip), companyId, managerId).shaped <> ({
      case (id, login, rights, addr, companyId, managerId) =>
        Member(id, login, rights, Address.tupled.apply(addr), companyId, managerId)
    }, { x: Member => Some((x.id, x.login, x.rights, Address.unapply(x.addr).get, x.companyId, x.managerId)) })

    def login = column[String]("login");
    def num = column[Int]("num");
    def road = column[String]("road");
    def zip = column[String]("zip");
    def rights = column[UserRights]("rights");
    def managerId = column[Option[Long]]("managerId");
    def companyId = column[Long]("companyId");
    def company = foreignKey("member2company", companyId, companyQuery)(_.id)
    def forInsert = (login, rights, (num, road, zip), companyId, managerId).shaped <> ({
      case (login, rights, add, companyId, managerId) =>
        Member(None, login, rights, Address.tupled.apply(addr), companyId, managerId)
    }, { x: Member => Some((x.login, x.rights, Address.unapply(x.addr).get, x.companyId, x.managerId)) })
  }
  val memberQuery = TableQuery[MemberTable]
  type MemberCrud = CrudEx[Member, MemberTable];

Many to many relationship mapping

Many to many relationship are defined by a reference to a list of objects of another mapped case class. The code below

  case class Project(name: String, company: Company, members: List[Member])

will generate :

• as usual, the ProjectTable class and the projectQuery object
• an association table between the Project and the Member table. The table name in this case is ```project2member````

  case class Project(id: Option[Long], name: String, companyId: Long) {
    def xid = id.getOrElse(throw new Exception("Object has no id yet"))
    def members = for {
                        x <- project2MemberQuery if x.projectId === id
                        y <- memberQuery if x.memberQuery === y.id
                  } yield y

    def company(implicit session: JdbcBackend#SessionDef) = companyQuery.where(_.id === companyId).first
    def addMember(memberId: Long)(implicit session: JdbcBackend#SessionDef) = project2MemberQuery.insert(Project2Member(xid, memberId))

  }

  class ProjectTable(tag: Tag) extends Table[Project](tag, "project") {
    def id = column[Long]("id", O.PrimaryKey, O.AutoInc)
    def * = id.? ~ name ~ companyId <> (Project.tupled, Project.unapply _)
    def name = column[String]("name");
    def companyId = column[Long]("companyId");
    def forInsert = (name ~ companyId).shaped <> (
                                { t => Project(None, t._1, t._2) }, 
                                { (x: Project) => Some((x.name, x.companyId)) });
    def companyFK = foreignKey("project2company", companyId, companyQuery)(_.id)
  }
  val projectQuery = TableQuery[ProjectTable]
  type ProjectCrud = CrudEx[Project, ProjectTable];

  case class Project2Member(projectId: Long, memberId: Long) {
    def project(implicit session: JdbcBackend#SessionDef) = projectQuery.where(_.id === projectId).first;
    def member(implicit session: JdbcBackend#SessionDef) = memberQuery.where(_.id === memberId).first
  }

  class Project2MemberTable(tag: Tag) extends Table[Project2Member](tag, "project2member") {
    def projectId = column[Long]("projectId");
    def memberId = column[Long]("memberId");
    def * = (projectId ~ memberId).shaped <> (Project2Member.tupled, Project2Member.unapply _)
    def projectFK = foreignKey("project2member2project", projectId, projectQuery)(_.id);
    def memberFK = foreignKey("project2member2member", memberId, memberQuery)(_.id)
  }
  val Project2Members = TableQuery[Project2MemberTable]