Introduction

Database schema defintion

To describe the database schema, we use one case-object per table which needs to extend the Table class. The values of the object should correspond to either:

• the columns of the table, in which case they are instances of Column[T], or
• relationships with other tables, in which case they are instances of ManyToMany, ManyToOne, or OneToMany.

case object Releases extends Table:
  val id = Column[Int](primary = true)
  val title = Column[String]()
  val country = Column[String]()
  val genre = Column[String]()
  
  val artists = ManyToMany(Artists, ReleasedBy, ReleasedBy.releaseId, ReleasedBy.artistId)
  val tracks = OneToMany(Tracks, Tracks.release)

  
case object Tracks extends Table:
  val id = Column[Int](primary = true)
  val releaseId = Column[Int]()
  val position = Column[String]()
  val title = Column[String]()
  val duration = Column[Int]()
  
  val release = ManyToOne(Releases, releaseId)


case object Artists extends Table:
  val id = Column[Int](primary = true)
  val name = Column[String]()
  val realName = Column[String]()
  val profile = Column[String]()
  val url = Column[String]

  val releases = ManyToMany(Releases, ReleasedBy, ReleasedBy.artistId, ReleasedBy.releaseId)


case object ReleasedBy extends Table:
  val releaseId = Column[Int]()
  val artistId = Column[Int]()

Queries

We can prepare queries by calling the function from(TableName). This will return an instance of QueryBuilder which has many of the convenient methods that we know from Scala sequences.

Mapping

Mapping gets translated into the SELECT clause in SQL.

// SELECT name, realname AS actualName FROM artists
from(Artists).map{ a => (a.name, a.realname.as("actualName")) }

Filtering

Filtering gets translated into the WHERE clause in SQL.

// SELECT id FROM artists WHERE name = 'Red Hot Chili Peppers'
from(Artists).filter(_.name === "Red Hot Chili Peppers").map(_.id)

Sorting

Sorting gets translated into the ORDER BY clause in SQL.

// SELECT * FROM artists ORDER BY name DESC, id ASC
from(Artists).sortBy{ a => (a.name.desc, a.id) }

Alternatively, we can use the sorted which sorts based on columns in the order in which they are selected. This is especially useful if we only select one or two columns.

// SELECT name, url FROM artists ORDER BY name DESC, url DESC
from(Artists).map{ a => (a.name, a.url) }.sorted(desc = true)

Limiting the number of results

// SELECT * FROM artists LIMIT 15 OFFSET 10
from(Artists).offset(10).limit(15)

Joins and subqueries

// SELECT *
// FROM releases
// WHERE EXISTS (
//   SELECT 1
//   FROM artists
//   JOIN released_by ON released_by.artist_id = artists.id
//   WHERE released_by.release_id = releases.id AND artists.name = 'Radiohead'
// )
from(Releases).filter(_.artists.exists(_.name === "Radiohead"))

// SELECT *
// FROM releases
// ORDER BY (
//   SELECT COUNT(*)
//   FROM artists
//   JOIN released_by ON released_by.artist_id = artists.id
//   WHERE released_by.release_id = releases.id
// )
from(Releases).sortBy(_.artists.count)

Scope of a query

Methods which can act upon the current scope of selection such as filter, map, or sortBy provide as an argument an instance of Scope refined object with all the values that are being selected. For example, the scope of from(Artists) is

Scope & {
	val id: Expression[Int]
	val position: Expression[String]
	val title: Expression[String]
	val duration: Expression[Int]
	val releaseId: Expression[Int]
	val release: TableScope[Release.type]
}

but the scope of

from(artists).map(a => (a.id, a.title, (a.duration * 2).as("doubleDuration")))

is

Scope & {
	val id: Expression[Int]
	val title: Expression[String]
	val doubleDuration: Expression[Int]
}