import java.lang.Integer
import com.novocode.squery._
import com.novocode.squery.Implicit._
import com.novocode.squery.session._
import com.novocode.squery.session.SessionFactory._

// Define table:
object Users extends Table[(Integer, String, String)]("users") {
  def id = intColumn("id", O.AutoInc, O.NotNull)
  def first = stringColumn("first")
  def last = stringColumn("last")
  def * = id ~ first ~ last
}

// Basic usage
val sf = new DriverManagerSessionFactory("org.h2.Driver", "jdbc:h2:mem:test1")
sf withSession {
  // Prepare a simple query
  val q1 = for(u <- Users) yield u

  // Print SQL statement to be executed:
  println(q1.selectStatement)  // displays SELECT t1.id,t1.first,t1.last FROM users t1

  // Print query result:
  for(t <- q1) println("User tuple: "+t)

  // Query statements can also be used with updates:
  val q = for(u <- Users if u.id is 42) yield u.first ~ u.last
  q.update("foo", "bar")
}

import com.twitter.querulous.evaluator.QueryEvaluator
val queryEvaluator = QueryEvaluator("host", "username", "password")
val users = queryEvaluator.select("SELECT * FROM users WHERE id IN (?) OR name = ?", List(1,2,3), "Jacques") { row =>
  new User(row.getInt("id"), row.getString("name"))
}
queryEvaluator.execute("INSERT INTO users VALUES (?, ?)", 1, "Jacques")
queryEvaluator.transaction { transaction =>
  transaction.select("SELECT ... FOR UPDATE", ...)
  transaction.execute("INSERT INTO users VALUES (?, ?)", 1, "Jacques")
  transaction.execute("INSERT INTO users VALUES (?, ?)", 2, "Luc")
}

// Defining tables and a schema:
import org.squeryl.PrimitiveTypeMode._

class Author(var id: Long,
             var firstName: String,
             var lastName: String)

class Book(var id: Long,
           var title: String,
           @Column("AUTHOR_ID") // the default 'exact match' policy can be overriden
           var authorId: Long,
           var coAuthorId: Option[Long]) {
  def this() = this(0,"",0,Some(0L))
}

object Library extends Schema {
  //When the table name doesn't match the class name, it is specified here :
  val authors = table[Author]("AUTHORS")
  val books = table[Book]
}

// Basic usage
Class.forName("org.postgresql.Driver");
val session = Session.create(
  java.sql.DriverManager.getConnection("jdbc:postgresql://localhost:5432/squeryl", "squeryl", "squeryl"),
  new PostgreSqlAdapter
)

//Squeryl database interaction must be done with a using block :
import Library._
using(session) {
  books.insert(new Author(1, "Michel","Folco"))
  val a = from(authors)(a=> where(a.lastName === "Folco") select(a))
}

case class Song(id: Option[Long], title: String, seconds: Short)
case class Album(id: Option[Long], title: String, year: Short, songs: IndexedSeq[Song])
case class Artist(id: Option[Long], name: String, albums: Set[Album])

object SongExtractor extends JoinExtractor[Song] {
  val key = Set("SONG_ID")

  def extract(row: Row, join: Join) = {
    new Song(
          row.bigInt("SONG_ID"),
          row.string("TITLE").get,
          row.smallInt("DURATION_SECONDS").get
        )
  }
}

object AlbumExtractor extends JoinExtractor[Album] {
  val key = Set("ALBUM_ID")

  def extract(row: Row, join: Join) = {
    new Album(
          row.bigInt("ALBUM_ID"),
          row.string("TITLE").get,
          row.smallInt("YEAR_ISSUED").get,
          join.extractSeq(SongExtractor, Map("TITLE"->"SONG_TITLE"))
        )
  }
}

object ArtistExtractor extends JoinExtractor[Artist] {
  val key = Set("ARTIST_ID")

  def extract(row: Row, join: Join) = {
    new Artist(
          row.bigInt("ARTIST_ID"),
          row.string("NAME"),
          join.extractSeq(AlbumExtractor)
        )
  }
}

val ds: javax.sql.DataSource = ...
val builder = new SQLFileBuilder(ds, new java.io.File("sql/"))
val broker = builder.build()

// Print all artists with their albums (if any)
val artists = broker.readOnly() { session =>
  session.selectAll[Artist]('selectArtist) // ' I wish they could fix the Scala Symbol formatting
}
for (ar <- artists) {
  println(a.name)
      if (ar.albums.isEmpty)
        println("t<No albums>")
      else for (al <- ar.albums) {
        println("t" + al.title)
        for (s <- al.songs) {
          println("tt" + (al.songs.indexOf(s)+1) + ". " + s.title)
        }
      }
}

// Create an SQL query
val selectCountries = SQL("Select * from Country")

// Transform the resulting Stream[Row] as a List[(String,String)]
val countries = selectCountries().map(row =>
    row[String]("code") -> row[String]("name")
).toList

val countries = SQL("Select name,population from Country")().collect {
    case Row("France", _) => France()
    case Row(name:String, pop:Int) if(pop > 1000000) => BigCountry(name)
    case Row(name:String, _) => SmallCountry(name)
}

SQL(
    """
        select * from Country c
        join CountryLanguage l on l.CountryCode = c.Code
        where c.code = {countryCode};
    """
).on("countryCode" -> "FRA")

class Category extends Record[Category] {
  val id = field(Category.id)
  val name = field(Category.name)
  val books = oneToMany(Book.category)    // allows navigating between associations transparently
}

object Category extends Table[Category] with LongIdPK[Category] {
  val name = stringColumn("name")         // creates a column
      .notNull                            // creates NOT NULL constraint
      .unique                             // creates UNIQUE constraint
      .validateNotEmpty                   // adds NotEmpty validation
      .validatePattern("^[a-zA-Z]{1,8}$") // adds Pattern validation
}

class Book extends Record[Book] {
  val id = field(Book.id)
  val title = field(Book.title)
  val category = manyToOne(Book.category)
}

object Book extends Table[Book] with LongIdPK[Book] {
  val title = stringColumn("title")
      .notNull
      .validateNotEmpty
  val category = longColumn("category_id")
      .references(Category)     // creates an association with Category
      .onDeleteSetNull          // specifies a foreign-key action
      .onUpdateCascade
}

new DDLExport(Category, Book).create   // creates database schema

// find category by id
val c = Category.get(2l)
// find all books
val allBooks = Book.all
// find books for category
val cBooks = c.get.books
// find books by title
Book.criteria.add("title" like "a%").list

select()
      .from(Category as "c" join (Book as "b"), Category as "c1")
      .where("c1.name" like "a%")
      .addOrder(asc("c.name"))
      .list

select(count("b.id"), "c.name").from(Category as "c" join (Book as "b")).list

import java.util.{List => JList}

val jdbcTemplate = new SimpleJdbcTemplate(dataSource)
val sql = "select name, age from my_obj"
val os: JList[MyObj] = jdbcTemplate.query(sql, new ParametrizedRowMapper[MyObj] {
  def mapRow(rs: ResultSet, row: Int) : MyObj = {
      //convert a row of a result set to a MyObj
      MyObj(rs.getString("name"), rs.getInt("age"))
  }
}

class MyClass extends Something
    with SimpleEntityManagerFactory
    with ThreadLocalEntityManager {

 def getPersistenceUnitName = "mip"
 . . .
}

@Entity
@Table(name = "obj_item")
@Inheritance(strategy = InheritanceType.JOINED)
@SequenceGenerator(name = "obj_item_id_seq", sequenceName = "obj_item_id_sequence",         allocationSize = 1)
class ObjectItem extends MIPEntity {
  @Id
  @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "obj_item_id_seq")
  @Column(name = "obj_item_id", nullable = false, length = 20)
  @BeanProperty
  var id: BigInteger = _

  @Column(name = "cat_code", nullable = false, length = 6)
  @BeanProperty
  var objItemCatCode: String = _
}

@Entity
@Table(name = "org_struct")
@IdClass(classOf[OrganisationStructureId])
@SequenceGenerator(name = "org_struct_index_seq", sequenceName = "org_struct_index_sequence", allocationSize = 1)
class OrganisationStructure extends MIPEntity {
  @Id
  @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "org_struct_index_seq")
  @Column(name = "org_struct_ix", nullable = false, length = 20)
  @BeanProperty
  protected var ix: BigInteger = _

  @Id
  @ManyToOne(fetch = FetchType.EAGER)
  @JoinColumn(name = "org_struct_root_org_id", nullable = false, updatable = false)
  @BeanProperty
  protected var orgStructRootOrg: Organisation = _

  . . .
}

class OrganisationStructureId {
  @BeanProperty
  var orgStructRootOrg: BigInteger = _
  @BeanProperty
  var ix: BigInteger = _
. . .
}

. . .
val filter: NameFilter = newFilterInstance(QueryId("FindObjectItemFromNameWithFilter"))
filter.name = "%Test%"

var i = 0
forQueryResults {
  oi: ObjectItem =>
    i = i + 1
} withQuery (filter)
i must_== 10
. . .

withTrxAndCommit {
 findAndApply(id ) {
   u:User => remove(u)
 }
}

withTrxAndCommit {
 oneResultQueryAndApply {
  d: Double =>
   eStatRet.setDistance(d)
  } withNativeQuery (QueryId("DistancePointFromTextToLocID"), postGISPoint, user.getUsersLocation.getId)
}

class Product(val name: String, val attributes: Set[Attribute])
class Attribute(val name: String, val value: String)
...

val product = new Product("blue jean", Set(new Attribute("colour", "blue"), new Attribute("size", "medium")))
val inserted = mapperDao.insert(ProductEntity, product)
// the persisted entity has an id property:
println("%d : %s".format(inserted.id,inserted))

val o=OrderEntity

import Query._
val orders = query(select from o where o.totalAmount >= 20.0 and o.totalAmount <= 30.0)
println(orders) // a list of orders

import java.sql.{Connection, DriverManager, ResultSet};

// Change to Your Database Config
val conn_str = "jdbc:mysql:/localhost:3306/DBNAME?user=DBUSER&password=DBPWD"

// Load the driver
classOf[com.mysql.jdbc.Driver]

// Setup the connection
val conn = DriverManager.getConnection(conn_str)
try {
    // Configure to be Read Only
    val statement = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY)

    // Execute Query
    val rs = statement.executeQuery("SELECT quote FROM quotes LIMIT 5")

    // Iterate Over ResultSet
    while (rs.next) {
      println(rs.getString("quote"))
    }
}
finally {
    conn.close
}

# dbhost(s), dbname, username, password, urlOptions, driverName
val queryEvaluator = QueryEvaluator("host", "dbname", "username", "password", Map[String,String](), "jdbc:postgresql")

val pe=PersonEntity //alias
val people=query(QueryConfig.pagination(2, 10),select from pe where pe.lives === house)