<commit_before>package org.scalaquery.test

import org.junit.Test
import org.junit.Assert._
import org.scalaquery.ql._
import org.scalaquery.ql.TypeMapper._
import org.scalaquery.ql.extended.{ExtendedTable => Table}
import org.scalaquery.session.Database.threadLocalSession
import org.scalaquery.simple.StaticQuery._
import org.scalaquery.ast._
import org.scalaquery.test.util._
import org.scalaquery.test.util.TestDB._

object NewQuerySemanticsTest extends DBTestObject(H2Mem)

class NewQuerySemanticsTest(tdb: TestDB) extends DBTest(tdb) {
  import tdb.driver.Implicit._

  @Test def test(): Unit = db withSession {

    val SuppliersStd = new Table[(Int, String, String, String, String, String)]("SUPPLIERS") {
      def id = column[Int]("SUP_ID", O.PrimaryKey) // This is the primary key column
      def name = column[String]("SUP_NAME")
      def street = column[String]("STREET")
      def city = column[String]("CITY")
      def state = column[String]("STATE")
      def zip = column[String]("ZIP")
      def * = id ~ name ~ street ~ city ~ state ~ zip
    }

    val CoffeesStd = new Table[(String, Int, Double, Int, Int)]("COFFEES") {
      def name = column[String]("COF_NAME", O.PrimaryKey)
      def supID = column[Int]("SUP_ID")
      def price = column[Double]("PRICE")
      def sales = column[Int]("SALES")
      def total = column[Int]("TOTAL")
      def * = name ~ supID ~ price ~ sales ~ total
      def supplier = foreignKey("SUP_FK", supID, SuppliersStd)(_.id)
    }

    val Suppliers = new Table[(Int, String, String)]("SUPPLIERS") {
      def id = column[Int]("SUP_ID", O.PrimaryKey) // This is the primary key column
      def name = column[String]("SUP_NAME")
      def street = column[String]("STREET")
      def city = column[String]("CITY")
      def state = column[String]("STATE")
      def zip = column[String]("ZIP")
      def * = id ~ name ~ street
      def forInsert = id ~ name ~ street ~ city ~ state ~ zip
    }

    val Coffees = new Table[(String, Int, Double, Int, Int)]("COFFEES") {
      def name = column[String]("COF_NAME", O.PrimaryKey)
      def supID = column[Int]("SUP_ID")
      def price = column[Double]("PRICE")
      def sales = column[Int]("SALES")
      def total = column[Int]("TOTAL")
      def * = name ~ supID ~ price ~ sales ~ (total * 10)
      def forInsert = name ~ supID ~ price ~ sales ~ total
      def totalComputed = sales.asColumnOf[Double] * price
      def supplier = foreignKey("SUP_FK", supID, Suppliers)(_.id)
    }

    (SuppliersStd.ddl ++ CoffeesStd.ddl).create
    (SuppliersStd.ddl ++ CoffeesStd.ddl).createStatements.foreach(s => println("create: "+s))

    SuppliersStd.insert(101, "Acme, Inc.",      "99 Market Street", "Groundsville", "CA", "95199")
    SuppliersStd.insert( 49, "Superior Coffee", "1 Party Place",    "Mendocino",    "CA", "95460")
    SuppliersStd.insert(150, "The High Ground", "100 Coffee Lane",  "Meadows",      "CA", "93966")

    CoffeesStd.insertAll(
      ("Colombian",         101, 7.99, 0, 0),
      ("French_Roast",       49, 7.99, 0, 0),
      ("Espresso",          150, 9.99, 0, 0),
      ("Colombian_Decaf",   101, 8.99, 0, 0),
      ("French_Roast_Decaf", 49, 9.99, 0, 0)
    )

    val l1 = queryNA[String]("select c.cof_name from coffees c").list
    println("l1: "+l1)
    assertEquals(5, l1.length)
    val l2 = queryNA[String]("select c.cof_name from coffees c, suppliers s").list
    println("l2: "+l2)
    assertEquals(15, l2.length)

    def show(name: String, g: Query[_,_]) {
      val n = Node(g)
      //AnonSymbol.assignNames(n, force = true)
      println("=========================================== "+name)
      n.dump("source: ")
      println
      val n2 = Optimizer(n)
      n2.dump("optimized: ")
      println
      val n3 = Relational(n2)
      n3.dump("relational: ")
      println
      println("SQL: "+g.selectStatement)
    }

    val q1 = for {
      c <- Coffees.sortBy(_.name).take(2)
      s <- Suppliers
    } yield (c.name ~ (s.city ++ ":"), c, s, c.totalComputed)
    show("q1: Plain implicit join", q1)
    val r1 = q1.to[Set]()
    println("r1: "+r1)
    val r1e = Set(
      (("Colombian","Groundsville:"),("Colombian",101,7.99,0,0),(101,"Acme, Inc.","99 Market Street"),0.0),
      (("Colombian","Mendocino:"),("Colombian",101,7.99,0,0),(49,"Superior Coffee","1 Party Place"),0.0),
      (("Colombian","Meadows:"),("Colombian",101,7.99,0,0),(150,"The High Ground","100 Coffee Lane"),0.0),
      (("Colombian_Decaf","Groundsville:"),("Colombian_Decaf",101,8.99,0,0),(101,"Acme, Inc.","99 Market Street"),0.0),
      (("Colombian_Decaf","Mendocino:"),("Colombian_Decaf",101,8.99,0,0),(49,"Superior Coffee","1 Party Place"),0.0),
      (("Colombian_Decaf","Meadows:"),("Colombian_Decaf",101,8.99,0,0),(150,"The High Ground","100 Coffee Lane"),0.0)
    )
    assertEquals(r1e, r1)

    val q1b_0 = Coffees.sortBy(_.price).take(3) join Suppliers on (_.supID === _.id)
    val q1b = for {
      (c, s) <- q1b_0.take(2).filter(_._1.name =!= "foo")
      (c2, s2) <- q1b_0
    } yield c.name ~ s.city ~ c2.name
    show("q1b: Explicit join with condition", q1b)
    val r1b = q1b.to[Set]()
    println("r1b: "+r1b)
    val r1be = Set(
      ("Colombian","Groundsville","French_Roast"),
      ("Colombian","Groundsville","Espresso"),
      ("Colombian","Groundsville","Colombian"),
      ("French_Roast","Mendocino","Espresso"),
      ("French_Roast","Mendocino","French_Roast"),
      ("French_Roast","Mendocino","Colombian")
    )

    /*val q2 = for {
      c <- Coffees.filter(_.price < 9.0).map(_.*)
      s <- Suppliers if s.id === c._2
    } yield c._1 ~ s.name
    show("q2: More elaborate query", q2)

    val q3 = Coffees.flatMap { c =>
      val cf = c.where(_.price < c.price)
      cf.flatMap { cf =>
        Suppliers.where(_.id === c.supID).map { s =>
          c.name ~ s.name ~ cf.name ~ cf.total ~ cf.totalComputed
        }
      }
    }
    show("q3: Lifting scalar values", q3)

    val q3b = Coffees.flatMap { c =>
      val cf = Query((c, 42)).where(_._1.price < 9.0)
      cf.flatMap { case (cf, num) =>
        Suppliers.where(_.id === c.supID).map { s =>
          c.name ~ s.name ~ cf.name ~ cf.total ~ cf.totalComputed ~ num
        }<commit_msg>More sensible unit test data<commit_after>package org.scalaquery.test

import org.junit.Test
import org.junit.Assert._
import org.scalaquery.ql._
import org.scalaquery.ql.TypeMapper._
import org.scalaquery.ql.extended.{ExtendedTable => Table}
import org.scalaquery.session.Database.threadLocalSession
import org.scalaquery.simple.StaticQuery._
import org.scalaquery.ast._
import org.scalaquery.test.util._
import org.scalaquery.test.util.TestDB._

object NewQuerySemanticsTest extends DBTestObject(H2Mem)

class NewQuerySemanticsTest(tdb: TestDB) extends DBTest(tdb) {
  import tdb.driver.Implicit._

  @Test def test(): Unit = db withSession {

    val SuppliersStd = new Table[(Int, String, String, String, String, String)]("SUPPLIERS") {
      def id = column[Int]("SUP_ID", O.PrimaryKey) // This is the primary key column
      def name = column[String]("SUP_NAME")
      def street = column[String]("STREET")
      def city = column[String]("CITY")
      def state = column[String]("STATE")
      def zip = column[String]("ZIP")
      def * = id ~ name ~ street ~ city ~ state ~ zip
    }

    val CoffeesStd = new Table[(String, Int, Double, Int, Int)]("COFFEES") {
      def name = column[String]("COF_NAME", O.PrimaryKey)
      def supID = column[Int]("SUP_ID")
      def price = column[Double]("PRICE")
      def sales = column[Int]("SALES")
      def total = column[Int]("TOTAL")
      def * = name ~ supID ~ price ~ sales ~ total
      def supplier = foreignKey("SUP_FK", supID, SuppliersStd)(_.id)
    }

    val Suppliers = new Table[(Int, String, String)]("SUPPLIERS") {
      def id = column[Int]("SUP_ID", O.PrimaryKey) // This is the primary key column
      def name = column[String]("SUP_NAME")
      def street = column[String]("STREET")
      def city = column[String]("CITY")
      def state = column[String]("STATE")
      def zip = column[String]("ZIP")
      def * = id ~ name ~ street
      def forInsert = id ~ name ~ street ~ city ~ state ~ zip
    }

    val Coffees = new Table[(String, Int, Double, Int, Int)]("COFFEES") {
      def name = column[String]("COF_NAME", O.PrimaryKey)
      def supID = column[Int]("SUP_ID")
      def price = column[Double]("PRICE")
      def sales = column[Int]("SALES")
      def total = column[Int]("TOTAL")
      def * = name ~ supID ~ price ~ sales ~ (total * 10)
      def forInsert = name ~ supID ~ price ~ sales ~ total
      def totalComputed = sales.asColumnOf[Double] * price
      def supplier = foreignKey("SUP_FK", supID, Suppliers)(_.id)
    }

    (SuppliersStd.ddl ++ CoffeesStd.ddl).create
    (SuppliersStd.ddl ++ CoffeesStd.ddl).createStatements.foreach(s => println("create: "+s))

    SuppliersStd.insert(101, "Acme, Inc.",      "99 Market Street", "Groundsville", "CA", "95199")
    SuppliersStd.insert( 49, "Superior Coffee", "1 Party Place",    "Mendocino",    "CA", "95460")
    SuppliersStd.insert(150, "The High Ground", "100 Coffee Lane",  "Meadows",      "CA", "93966")

    CoffeesStd.insertAll(
      ("Colombian",         101, 7.99, 0, 0),
      ("French_Roast",       49, 7.99, 0, 0),
      ("Espresso",          150, 9.99, 0, 0),
      ("Colombian_Decaf",   101, 8.49, 0, 0),
      ("French_Roast_Decaf", 49, 9.99, 0, 0)
    )

    val l1 = queryNA[String]("select c.cof_name from coffees c").list
    println("l1: "+l1)
    assertEquals(5, l1.length)
    val l2 = queryNA[String]("select c.cof_name from coffees c, suppliers s").list
    println("l2: "+l2)
    assertEquals(15, l2.length)

    def show(name: String, g: Query[_,_]) {
      val n = Node(g)
      //AnonSymbol.assignNames(n, force = true)
      println("=========================================== "+name)
      n.dump("source: ")
      println
      val n2 = Optimizer(n)
      n2.dump("optimized: ")
      println
      val n3 = Relational(n2)
      n3.dump("relational: ")
      println
      println("SQL: "+g.selectStatement)
    }

    val q1 = for {
      c <- Coffees.sortBy(_.name).take(2)
      s <- Suppliers
    } yield (c.name ~ (s.city ++ ":"), c, s, c.totalComputed)
    show("q1: Plain implicit join", q1)
    val r1 = q1.to[Set]()
    println("r1: "+r1)
    val r1e = Set(
      (("Colombian","Groundsville:"),("Colombian",101,7.99,0,0),(101,"Acme, Inc.","99 Market Street"),0.0),
      (("Colombian","Mendocino:"),("Colombian",101,7.99,0,0),(49,"Superior Coffee","1 Party Place"),0.0),
      (("Colombian","Meadows:"),("Colombian",101,7.99,0,0),(150,"The High Ground","100 Coffee Lane"),0.0),
      (("Colombian_Decaf","Groundsville:"),("Colombian_Decaf",101,8.49,0,0),(101,"Acme, Inc.","99 Market Street"),0.0),
      (("Colombian_Decaf","Mendocino:"),("Colombian_Decaf",101,8.49,0,0),(49,"Superior Coffee","1 Party Place"),0.0),
      (("Colombian_Decaf","Meadows:"),("Colombian_Decaf",101,8.49,0,0),(150,"The High Ground","100 Coffee Lane"),0.0)
    )
    assertEquals(r1e, r1)

    val q1b_0 = Coffees.sortBy(_.price).take(3) join Suppliers on (_.supID === _.id)
    val q1b = for {
      (c, s) <- q1b_0.take(2).filter(_._1.name =!= "Colombian")
      (c2, s2) <- q1b_0
    } yield c.name ~ s.city ~ c2.name
    show("q1b: Explicit join with condition", q1b)
    val r1b = q1b.to[Set]()
    println("r1b: "+r1b)
    val r1be = Set(
      ("French_Roast","Mendocino","Colombian"),
      ("French_Roast","Mendocino","French_Roast"),
      ("French_Roast","Mendocino","Colombian_Decaf")
    )
    assertEquals(r1be, r1b)

    /*val q2 = for {
      c <- Coffees.filter(_.price < 9.0).map(_.*)
      s <- Suppliers if s.id === c._2
    } yield c._1 ~ s.name
    show("q2: More elaborate query", q2)

    val q3 = Coffees.flatMap { c =>
      val cf = c.where(_.price < c.price)
      cf.flatMap { cf =>
        Suppliers.where(_.id === c.supID).map { s =>
          c.name ~ s.name ~ cf.name ~ cf.total ~ cf.totalComputed
        }
      }
    }
    show("q3: Lifting scalar values", q3)

    val q3b = Coffees.flatMap { c =>
      val cf = Query((c, 42)).where(_._1.price < 9.0)
      cf.flatMap { case (cf, num) =>
        Suppliers.where(_.id === c.supID).map { s =>
          c.name ~ s.name ~ cf.name ~ cf.total ~ cf.totalComputed ~ num
        }
{'fraction_non_alphanumeric': 0.18034644649506232, 'fraction_numerical': 0.03593977659057795, 'mean_word_length': 3.0694993412384717, 'pattern_counts': {'":': 2, '<': 21, '<?xml version=': 0, '>': 19, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '7320021', 'n_tokens_mistral': 4706, 'n_tokens_neox': 4444, 'n_words': 959}