<commit_before>/*******************************************************************************
 * Copyright 2010 <NAME>
 * 
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 * 
 *   http://www.apache.org/licenses/LICENSE-2.0
 * 
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 ******************************************************************************/
package org.squeryl.tests.musicdb

import java.sql.SQLException
import org.squeryl.tests.QueryTester
import org.squeryl._
import dsl.{Measures, GroupWithMeasures}

class MusicDbObject extends KeyedEntity[Int] {
  val id: Int = 0
}

class Person(var firstName:String, var lastName: String) extends MusicDbObject

class Song(val title: String, val authorId: Int, val interpretId: Int, val cdId: Int) extends MusicDbObject

class Cd(var title: String, var mainArtist: Int, var year: Int) extends MusicDbObject


class MusicDb extends Schema with QueryTester {

  import org.squeryl.PrimitiveTypeMode._

  val songs = table[Song]

  val artists = table[Person]

  val cds = table[Cd]

  val testInstance = new {

    try {
      drop // *NOT* something to do in real life...
    }
    catch {
      case e:SQLException => {} 
    }

    create

    val herbyHancock = artists.insert(new Person("Herby", "Hancock"))
    val ponchoSanchez = artists.insert(new Person("Poncho", "Sanchez"))
    val mongoSantaMaria = artists.insert(new Person("Mongo", "Santa Maria"))
    val alainCaron = artists.insert(new Person("Alain", "Caron"))
    val hossamRamzy = artists.insert(new Person("Hossam", "Ramzy"))

    val congaBlue = cds.insert(new Cd("Conga Blue", ponchoSanchez.id, 1998))
    val   watermelonMan = songs.insert(new Song("Watermelon Man", herbyHancock.id, ponchoSanchez.id, congaBlue.id))
    val   besameMama = songs.insert(new Song("Besame Mama", mongoSantaMaria.id, ponchoSanchez.id, congaBlue.id))

    val freedomSoundAlbum = cds.insert(new Cd("Freedom Sound", ponchoSanchez.id, 1997))
    val   freedomSound = songs.insert(new Song("Freedom Sound", ponchoSanchez.id, ponchoSanchez.id, freedomSoundAlbum.id))
        

    val expectedSongCountPerAlbum = List((congaBlue.title,2), (freedomSoundAlbum.title, 1))
  }

  val basicSelectUsingWhereOnQueryable =
    artists.where(a=> a.id === testInstance.mongoSantaMaria.id)

  val basicSelectUsingWhereOnQueryableNested =
    basicSelectUsingWhereOnQueryable.where(a=> a.id === testInstance.mongoSantaMaria.id)
  
  lazy val poncho =
   from(artists)(a =>
      where(a.firstName === "Poncho") select(a)
   )

  def selfJoinNested3Level = 
    from(
      from(
        from(artists)(a =>   where(a.id === testInstance.ponchoSanchez.id) select(a))
      )(a =>   select(a))
    )(a =>   select(a))

  def selfJoinNested4LevelPartialSelect =
    from(selfJoinNested3Level)(a => where(a.id gt -1) select(a.lastName))

  lazy val songsFeaturingPoncho =
    from(songs, artists)((s,a) =>
      where(a.firstName === "Poncho" and s.interpretId === a.id)
      select(s)
      orderBy(s.title, a.id desc)
    )

  lazy val songsFeaturingPonchoNestedInWhere =
    from(songs, artists)((s,a) =>
      where(
        s.interpretId in from(artists)(a => where(a.firstName === "Poncho") select(a.id))
      )
      select(s)
      orderBy(s.title asc)
    ).distinct

  lazy val yearOfCongaBluePlus1 =
    from(cds)(cd =>
      where(cd.title === testInstance.congaBlue.title)
      select(&(cd.year plus 1))
    )
  
  def songCountPerAlbum(cds: Queryable[Cd]) =
    from(cds, songs)((cd, song) =>
      where(song.cdId === cd.id)
      groupBy(cd.title) compute(count)
      orderBy(cd.title)
    )

  lazy val songCountPerAlbumFeaturingPoncho = songCountPerAlbum(
      from(songs, artists, cds)((s, a, cd) =>
        where(a.firstName === "Poncho" and s.interpretId === a.id and s.cdId === cd.id)
        select(cd)
      ).distinct
    )

  lazy val songsFeaturingPonchoNestedInFrom =
    from(songs, poncho)((s,a) =>
      where(s.interpretId === a.id)
      select((s,a.firstName))
      orderBy(s.title)
    )

  def songCountPerAlbumId(cds: Queryable[Cd]) =
    from(cds, songs)((cd, song) =>
      where(song.cdId === cd.id)
      groupBy(cd.id) compute(count)
    )

  lazy val songCountPerAlbumIdJoinedWithAlbum  =
    from(songCountPerAlbumId(cds), cds)((sc, cd) =>
      where(sc.key === cd.id)
      select((cd.title, sc.measures))
      orderBy(cd.title)
    )

  def songCountPerAlbumIdJoinedWithAlbumNested =
    from(songCountPerAlbumIdJoinedWithAlbum)(q =>
      select(q)
    )

  //TODO: list2Queryable conversion using 'select x0 as x from dual union ...'
  def artistsInvolvedInSongs(songIds: List[Int]) =
    from(
      from(songs)(s =>
        where((s.authorId in songIds) or (s.interpretId in songIds))
        select(s)
      ),<commit_msg>fixed false negative test case (caused by non ordering of query)<commit_after>/*******************************************************************************
 * Copyright 2010 <NAME>
 * 
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 * 
 *   http://www.apache.org/licenses/LICENSE-2.0
 * 
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 ******************************************************************************/
package org.squeryl.tests.musicdb

import java.sql.SQLException
import org.squeryl.tests.QueryTester
import org.squeryl._
import dsl.{Measures, GroupWithMeasures}

class MusicDbObject extends KeyedEntity[Int] {
  val id: Int = 0
}

class Person(var firstName:String, var lastName: String) extends MusicDbObject

class Song(val title: String, val authorId: Int, val interpretId: Int, val cdId: Int) extends MusicDbObject

class Cd(var title: String, var mainArtist: Int, var year: Int) extends MusicDbObject


class MusicDb extends Schema with QueryTester {

  import org.squeryl.PrimitiveTypeMode._

  val songs = table[Song]

  val artists = table[Person]

  val cds = table[Cd]

  val testInstance = new {

    try {
      drop // *NOT* something to do in real life...
    }
    catch {
      case e:SQLException => {} 
    }

    create

    val herbyHancock = artists.insert(new Person("Herby", "Hancock"))
    val ponchoSanchez = artists.insert(new Person("Poncho", "Sanchez"))
    val mongoSantaMaria = artists.insert(new Person("Mongo", "Santa Maria"))
    val alainCaron = artists.insert(new Person("Alain", "Caron"))
    val hossamRamzy = artists.insert(new Person("Hossam", "Ramzy"))

    val congaBlue = cds.insert(new Cd("Conga Blue", ponchoSanchez.id, 1998))
    val   watermelonMan = songs.insert(new Song("Watermelon Man", herbyHancock.id, ponchoSanchez.id, congaBlue.id))
    val   besameMama = songs.insert(new Song("Besame Mama", mongoSantaMaria.id, ponchoSanchez.id, congaBlue.id))

    val freedomSoundAlbum = cds.insert(new Cd("Freedom Sound", ponchoSanchez.id, 1997))
    val   freedomSound = songs.insert(new Song("Freedom Sound", ponchoSanchez.id, ponchoSanchez.id, freedomSoundAlbum.id))
        

    val expectedSongCountPerAlbum = List((congaBlue.title,2), (freedomSoundAlbum.title, 1))
  }

  val basicSelectUsingWhereOnQueryable =
    artists.where(a=> a.id === testInstance.mongoSantaMaria.id)

  val basicSelectUsingWhereOnQueryableNested =
    basicSelectUsingWhereOnQueryable.where(a=> a.id === testInstance.mongoSantaMaria.id)
  
  lazy val poncho =
   from(artists)(a =>
      where(a.firstName === "Poncho") select(a)
   )

  def selfJoinNested3Level = 
    from(
      from(
        from(artists)(a =>   where(a.id === testInstance.ponchoSanchez.id) select(a))
      )(a =>   select(a))
    )(a =>   select(a))

  def selfJoinNested4LevelPartialSelect =
    from(selfJoinNested3Level)(a => where(a.id gt -1) select(a.lastName))

  lazy val songsFeaturingPoncho =
    from(songs, artists)((s,a) =>
      where(a.firstName === "Poncho" and s.interpretId === a.id)
      select(s)
      orderBy(s.title, a.id desc)
    )

  lazy val songsFeaturingPonchoNestedInWhere =
    from(songs, artists)((s,a) =>
      where(
        s.interpretId in from(artists)(a => where(a.firstName === "Poncho") select(a.id))
      )
      select(s)
      orderBy(s.title asc)
    ).distinct

  lazy val yearOfCongaBluePlus1 =
    from(cds)(cd =>
      where(cd.title === testInstance.congaBlue.title)
      select(&(cd.year plus 1))
    )
  
  def songCountPerAlbum(cds: Queryable[Cd]) =
    from(cds, songs)((cd, song) =>
      where(song.cdId === cd.id)
      groupBy(cd.title) compute(count)
      orderBy(cd.title)
    )

  lazy val songCountPerAlbumFeaturingPoncho = songCountPerAlbum(
      from(songs, artists, cds)((s, a, cd) =>
        where(a.firstName === "Poncho" and s.interpretId === a.id and s.cdId === cd.id)
        select(cd)
      ).distinct
    )

  lazy val songsFeaturingPonchoNestedInFrom =
    from(songs, poncho)((s,a) =>
      where(s.interpretId === a.id)
      select((s,a.firstName))
      orderBy(s.title)
    )

  def songCountPerAlbumId(cds: Queryable[Cd]) =
    from(cds, songs)((cd, song) =>
      where(song.cdId === cd.id)
      groupBy(cd.id) compute(count)
    )

  lazy val songCountPerAlbumIdJoinedWithAlbum  =
    from(songCountPerAlbumId(cds), cds)((sc, cd) =>
      where(sc.key === cd.id)
      select((cd.title, sc.measures))
      orderBy(cd.title)
    )

  def songCountPerAlbumIdJoinedWithAlbumNested =
    from(songCountPerAlbumIdJoinedWithAlbum)(q =>
      select(q)
      orderBy(q._1)
    )

  //TODO: list2Queryable conversion using 'select x0 as x from dual union ...'
  def artistsInvolvedInSongs(songIds: List[Int]) =
    from(
      from(songs)(s =>
        where((s.authorId in songIds) or (s.interpretId in songIds))
        select(s)
      ),
{'fraction_non_alphanumeric': 0.14933256506290213, 'fraction_numerical': 0.0052818592144434845, 'mean_word_length': 3.5695480473892056, 'pattern_counts': {'":': 0, '<': 5, '<?xml version=': 0, '>': 43, 'https://': 0, 'lorem ipsum': 0, 'www.': 2, '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': '2399341', 'n_tokens_mistral': 3558, 'n_tokens_neox': 3359, 'n_words': 813}