gistfile1.scala

    for {
      _ <- actions(
        (suppliersStd.schema ++ coffeesStd.schema).create,
        suppliersStd += (101, "Acme, Inc.",      "99 Market Street", "Groundsville", "CA", "95199"),
        suppliersStd += ( 49, "Superior Coffee", "1 Party Place",    "Mendocino",    "CA", "95460"),
        suppliersStd += (150, "The High Ground", "100 Coffee Lane",  "Meadows",      "CA", "93966"),
        coffeesStd ++= Seq(
          ("Colombian",         101, 799, 1, 0),
          ("French_Roast",       49, 799, 2, 0),
          ("Espresso",          150, 999, 3, 0),
          ("Colombian_Decaf",   101, 849, 4, 0),
          ("French_Roast_Decaf", 49, 999, 5, 0)
        )
      ).named("setup")

      qa = for {
        c <- coffees.take(3)
      } yield (c.supID, (c.name, 42))
      ra <- qa.result.map(_.toSet)
      _ = ra.size shouldBe 3
      // No sorting, so result contents can vary
      _ = ra shouldAllMatch { case (s: Int, (i: String, 42)) => () }

      qb = qa.take(2).map(_._2)
      rb <- qb.result.map(_.toSet)
      _ = rb.size shouldBe 2
      // No sorting, so result contents can vary
      _ = rb shouldAllMatch { case (i: String, 42) => () }

      qb2 = qa.map(n => n).take(2).map(_._2)
      rb2 <- qb2.result.map(_.toSet)
      _ = rb2.size shouldBe 2
      // No sorting, so result contents can vary
      _ = rb2 shouldAllMatch { case (i: String, 42) => () }

      qc = qa.map(_._2).take(2)
      rc <- qc.result.map(_.toSet)
      _ = rc.size shouldBe 2
      // No sorting, so result contents can vary
      _ = rc shouldAllMatch { case (i: String, 42) => () }

      // Plain table
      q0 = coffees
      r0 <- q0.result.named("Plain table").map(_.toSet)
      _ = r0 shouldBe Set(
        ("Colombian",         101, 799, 1, 0),
        ("French_Roast",       49, 799, 2, 0),
        ("Espresso",          150, 999, 3, 0),
        ("Colombian_Decaf",   101, 849, 4, 0),
        ("French_Roast_Decaf", 49, 999, 5, 0)
      )

      // Plain implicit join
      q1 = for {
        c <- coffees.sortBy(c => (c.name, c.price.desc)).take(2)
        s <- suppliers
      } yield ((c.name, (s.city ++ ":")), c, s, c.totalComputed)
      r1 <- q1.result.named("Plain implicit join").map(_.toSet)
      _ = r1 shouldBe Set(
        (("Colombian","Groundsville:"),("Colombian",101,799,1,0),(101,"Acme, Inc.","99 Market Street"),799),
        (("Colombian","Mendocino:"),("Colombian",101,799,1,0),(49,"Superior Coffee","1 Party Place"),799),
        (("Colombian","Meadows:"),("Colombian",101,799,1,0),(150,"The High Ground","100 Coffee Lane"),799),
        (("Colombian_Decaf","Groundsville:"),("Colombian_Decaf",101,849,4,0),(101,"Acme, Inc.","99 Market Street"),3396),
        (("Colombian_Decaf","Mendocino:"),("Colombian_Decaf",101,849,4,0),(49,"Superior Coffee","1 Party Place"),3396),
        (("Colombian_Decaf","Meadows:"),("Colombian_Decaf",101,849,4,0),(150,"The High Ground","100 Coffee Lane"),3396)
      )

      // Explicit join with condition
      _ <- ifCap(rcap.pagingNested) {
        val q1b_0 = coffees.sortBy(_.price).take(3) join suppliers on (_.supID === _.id)
        val q1b = for {
          (c, s) <- q1b_0.sortBy(_._1.price).take(2).filter(_._1.name =!= "Colombian")
          (c2, s2) <- q1b_0
        } yield (c.name, s.city, c2.name)
        q1b.result.named("Explicit join with condition").map { r1b =>
          r1b shouldBe Set(
            ("French_Roast","Mendocino","Colombian"),
            ("French_Roast","Mendocino","French_Roast"),
            ("French_Roast","Mendocino","Colombian_Decaf")
          )
        }
      }

      // More elaborate query
      q2 = for {
        c <- coffees.filter(_.price < 900).map(_.*)
        s <- suppliers if s.id === c._2
      } yield (c._1, s.name)
      r2 <- q2.result.named("More elaborate query").map(_.toSet)
      _ = r2 shouldBe Set(
        ("Colombian","Acme, Inc."),
        ("French_Roast","Superior Coffee"),
        ("Colombian_Decaf","Acme, Inc.")
      )

      // Lifting scalar values
      q3 = coffees.flatMap { c =>
        val cf = Query(c).filter(_.price === 849)
        cf.flatMap { cf =>
          suppliers.filter(_.id === c.supID).map { s =>
            (c.name, s.name, cf.name, cf.total, cf.totalComputed)
          }
        }
      }
      r3 <- q3.result.named("Lifting scalar values").map(_.toSet)
      _ = r3 shouldBe Set(("Colombian_Decaf","Acme, Inc.","Colombian_Decaf",0,3396))

      // Lifting scalar values, with extra tuple
      q3b = coffees.flatMap { c =>
        val cf = Query((c, 42)).filter(_._1.price < 900)
        cf.flatMap { case (cf, num) =>
          suppliers.filter(_.id === c.supID).map { s =>
            (c.name, s.name, cf.name, cf.total, cf.totalComputed, num)
          }
        }
      }
      r3b <- q3b.result.named("Lifting scalar values, with extra tuple").map(_.toSet)
      _ = r3b shouldBe Set(
        ("Colombian","Acme, Inc.","Colombian",0,799,42),
        ("French_Roast","Superior Coffee","French_Roast",0,1598,42),
        ("Colombian_Decaf","Acme, Inc.","Colombian_Decaf",0,3396,42)
      )

      // Map to tuple, then filter
      _ <- ifCap(rcap.pagingNested) {
        val q4 = for {
          c <- coffees.map(c => (c.name, c.price, 42)).sortBy(_._1).take(2).filter(_._2 < 800)
        } yield (c._1, c._3)
        q4.result.named("Map to tuple, then filter").map { r4 =>
          r4.toSet shouldBe Set(("Colombian",42))
        }
      }

      // Map to tuple, then filter, with self-join
      q4b_0 = coffees.map(c => (c.name, c.price, 42)).filter(_._2 < 800)
      q4b = for {
        c <- q4b_0
        d <- q4b_0
      } yield (c,d)
      r4b <- q4b.result.map(_.toSet)
      _ = r4b shouldBe Set(
        (("Colombian",799,42),("Colombian",799,42)),
        (("Colombian",799,42),("French_Roast",799,42)),
        (("French_Roast",799,42),("Colombian",799,42)),
        (("French_Roast",799,42),("French_Roast",799,42))
      )

      // Implicit self-join
      q5_0 = coffees.sortBy(_.price).take(2)
      q5 = for {
        c1 <- q5_0
        c2 <- q5_0
      } yield (c1, c2)
      r5 <- q5.result.map(_.toSet)
      _ = r5 shouldBe Set(
        (("Colombian",101,799,1,0),("Colombian",101,799,1,0)),
        (("Colombian",101,799,1,0),("French_Roast",49,799,2,0)),
        (("French_Roast",49,799,2,0),("Colombian",101,799,1,0)),
        (("French_Roast",49,799,2,0),("French_Roast",49,799,2,0))
      )

      // Explicit self-join with condition
      q5b = for {
        t <- q5_0 join q5_0 on (_.name === _.name)
      } yield (t._1, t._2)
      r5b <- q5b.result.named("Explicit self-join with condition").map(_.toSet)
      _ = r5b shouldBe Set(
        (("Colombian",101,799,1,0),("Colombian",101,799,1,0)),
        (("French_Roast",49,799,2,0),("French_Roast",49,799,2,0))
      )

      // Unused outer query result, unbound TableQuery
      q6 = coffees.flatMap(c => suppliers)
      r6 <- q6.result.named("Unused outer query result, unbound TableQuery").map(_.toSet)
      _ = r6 shouldBe Set(
        (101,"Acme, Inc.","99 Market Street"),
        (49,"Superior Coffee","1 Party Place"),
        (150,"The High Ground","100 Coffee Lane")
      )

      // Simple union
      q7a = for {
        c <- coffees.filter(_.price < 800) union coffees.filter(_.price > 950)
      } yield (c.name, c.supID, c.total)
      r7a <- q7a.result.named("Simple union").map(_.toSet)
      _ = r7a shouldBe Set(
        ("Colombian",101,0),
        ("French_Roast",49,0),
        ("Espresso",150,0),
        ("French_Roast_Decaf",49,0)
      )

      // Union
      q7 = for {
        c <- coffees.filter(_.price < 800).map((_, 1)) union coffees.filter(_.price > 950).map((_, 2))
      } yield (c._1.name, c._1.supID, c._2)
      r7 <- q7.result.named("Union").map(_.toSet)
      _ = r7 shouldBe Set(
        ("Colombian",101,1),
        ("French_Roast",49,1),
        ("Espresso",150,2),
        ("French_Roast_Decaf",49,2)
      )

      // Transitive push-down without union
      q71 = for {
        c <- coffees.filter(_.price < 800).map((_, 1))
      } yield (c._1.name, c._1.supID, c._2)
      r71 <- q71.result.named("Transitive push-down without union").map(_.toSet)
      _ = r71 shouldBe Set(
        ("Colombian",101,1),
        ("French_Roast",49,1)
      )

      // Union with filter on the outside
      q7b = q7 filter (_._1 =!= "Colombian")
      r7b <- q7b.result.named("Union with filter on the outside").map(_.toSet)
      _ = r7b shouldBe Set(
        ("French_Roast",49,1),
        ("Espresso",150,2),
        ("French_Roast_Decaf",49,2)
      )

      // Outer join
      q8 = for {
        (c1, c2) <- coffees.filter(_.price < 900) joinLeft coffees.filter(_.price < 800) on (_.name === _.name)
      } yield (c1.name, c2.map(_.name))
      r8 <- q8.result.named("Outer join").map(_.toSet)
      _ = r8 shouldBe Set(
        ("Colombian",Some("Colombian")),
        ("French_Roast",Some("French_Roast")),
        ("Colombian_Decaf",None)
      )

      // Nested outer join
      q8b = for {
        t <- coffees.sortBy(_.sales).take(1) joinLeft coffees.sortBy(_.sales).take(2) on (_.name === _.name) joinLeft coffees.sortBy(_.sales).take(4) on (_._1.supID === _.supID)
      } yield (t._1, t._2)
      r8b <- q8b.result.named("Nested outer join").map(_.toSet)
      _ = r8b shouldBe Set(
        ((("Colombian",101,799,1,0),Some(("Colombian",101,799,1,0))),Some(("Colombian",101,799,1,0))),
        ((("Colombian",101,799,1,0),Some(("Colombian",101,799,1,0))),Some(("Colombian_Decaf",101,849,4,0)))
      )

      _ <- (suppliersStd.schema ++ coffeesStd.schema).drop
    } yield ()