Slick database Schema

Database Schema

Before we started Slick Programming (2): Preparing the development environment to generate Slick definitions of database tables using automated code generation tools (using the Lifted Embedding API), this article describes how to write these Schema definitions manually.

Database table Tables

In order to be able to define type-safe queries using Slick's Lifted Embedding API, we first need to define the classes that the database table represents for each row of data in the table and the TableQuery values for Schema corresponding to the database table, so let's look at the relevant definitions of the automatically generated Album table:

/** Entity class storing rows of table Album
   *  @param albumid Database column AlbumId PrimaryKey
   *  @param title Database column Title 
   *  @param artistid Database column ArtistId  */
  case class AlbumRow(albumid: Int, title: String, artistid: Int)
  /** GetResult implicit for fetching AlbumRow objects using plain SQL queries */
  implicit def GetResultAlbumRow(implicit e0: GR[Int], e1: GR[String]): GR[AlbumRow] = GR{
    prs => import prs._
    AlbumRow.tupled((<<[Int], <<[String], <<[Int]))
  }
  /** Table description of table Album. Objects of this class serve as prototypes for rows in queries. */
  class Album(tag: Tag) extends Table[AlbumRow](tag, "Album") {
    def * = (albumid, title, artistid) <> (AlbumRow.tupled, AlbumRow.unapply)
    /** Maps whole row to an option. Useful for outer joins. */
    def ? = (albumid.?, title.?, artistid.?).shaped.<>(
        {r=>import r._; _1.map(_=> AlbumRow.tupled((_1.get, _2.get, _3.get)))}, 
        (_:Any) =>  throw new Exception("Inserting into ? projection not supported."))

    /** Database column AlbumId PrimaryKey */
    val albumid: Column[Int] = column[Int]("AlbumId", O.PrimaryKey)
    /** Database column Title  */
    val title: Column[String] = column[String]("Title")
    /** Database column ArtistId  */
    val artistid: Column[Int] = column[Int]("ArtistId")

    /** Foreign key referencing Artist (database name FK_AlbumArtistId) */
    lazy val artistFk = foreignKey("FK_AlbumArtistId", artistid, Artist)
        (r => r.artistid, onUpdate=ForeignKeyAction.NoAction, onDelete=ForeignKeyAction.NoAction)
  }
  /** Collection-like TableQuery object for table Album */
  lazy val Album = new TableQuery(tag => new Album(tag))

All fields (Column) are defined using the column method, with one Scala type and one field name (corresponding to the definition of the database table) for each field, following the basic data types supported by Slick:

• Numeric types: Byte, Short, Int, Long, BigDecimal, Float, Double
• LOB types: java.sql.Blob, java.sql.Clob, Array[Byte]
• Date types: java.sql.Date, java.sql.Time, java.sql.Timestamp
• Boolean
• String
• Unit
• java.util.UUID

Null-supported fields are represented by Option(T), where T is the basic data type above, and after the field name, you can use some optional field definitions that are defined in table's O object. Below are common definitions

PrimaryKey indicates that the field is the primary key Default (defaultValue: T) The field defaults DBType (dbType: String) non-standard field type, such as DBType ("VARCHAR(20)") as a string type Auto Inc auto-add field NotNull, Nullable indicates whether the field can be empty

Each table definition requires a method that defines the default map, which defines the type of data when the query is executed to return a row of tables, Slick's does not require a mapping of the definition of the database table, and you can add a field (composite field) or omit a field.

The matching table definition

You can use a custom data type as a ""

Like what:

def * = (albumid, title, artistid) <> (AlbumRow.tupled, AlbumRow.unapply)

Constraints

Foreign key constraints can be defined using foreignKey

/** Foreign key referencing Artist (database name FK_AlbumArtistId) */
    lazy val artistFk = foreignKey("FK_AlbumArtistId", artistid, Artist)
        (r => r.artistid, onUpdate=ForeignKeyAction.NoAction, onDelete=ForeignKeyAction.NoAction)

Its parameters are the name of the foreign key constraint, the name of the field in this table, the name of the table where the foreign key is located, and a function that defines the foreign key constraint, as well as the behavior when updating and deleting the foreign key)

Primary key constraints can be defined using primaryKey, which is primarily used to define the case of composite primary keys

/** Primary key of Playlisttrack (database name PlaylistTrack_PK) */
    val pk = primaryKey("PlaylistTrack_PK", (playlistid, trackid))

Other cases, such as indexing, are very similar to primary key constraints, such as:

class A(tag: Tag) extends Table[(Int, Int)](tag, "a") {
    def k1 = column[Int]("k1")
    def k2 = column[Int]("k2")
    def * = (k1, k2)
    def idx = index("idx_a", (k1, k2), unique = true)
    // compiles to SQL:
    //   create unique index "idx_a" on "a" ("k1","k2")
}

The database defines the language DDL

Database definition statements can use TableQuery's ddl method, and multiple DDL objects can use a connection, such as:

val ddl = coffees.ddl ++ suppliers.ddl
db withDynSession {
    ddl.create
    //...
    ddl.drop
}

ddl.create and ddl.drop can create tables and delete tables, which you can use if you need to look at the corresponding SQL statements

val ddl = Album.ddl
ddl.createStatements.foreach(println)
ddl.dropStatements.foreach(println)

The corresponding MySQL statement is

create table `Album` (`AlbumId` INTEGER NOT NULL PRIMARY KEY,`Title` VARCHAR(254) NOT NULL,`ArtistId` INTEGER NOT NULL)
alter table `Album` add constraint `FK_AlbumArtistId` foreign key(`ArtistId`) references `Artist`(`ArtistId`) on update NO ACTION on delete NO ACTION
ALTER TABLE Album DROP FOREIGN KEY FK_AlbumArtistId
drop table `Album`