Data access test with Squeryl

README.md

Squeryl is O/R Mapper witten in scala

Environment

• Mac OSX 10.6.8
• MySQL 5.5.10
• scala 2.9.0.1
• sbt 0.7.7

Schema

• Table: users
  • columns
    • user_id bigint
    • screen_name varchar(128)
    • created_at datetime
  • primary key
    • user_id
• Table: statuses
  • columns
    • id bigint
    • text varchar(128)
    • created_at datetime
    • user_id bigint
  • primary key
    • id
  • foreign key
    • user_id

models.scala

package com.example.models

import org.squeryl.PrimitiveTypeMode._
import org.squeryl.Schema
import org.squeryl.KeyedEntity
import org.squeryl.annotations.Column
import org.squeryl.dsl._

import java.util.Date

class User (
  @Column("user_id")
  val id: Long,
  @Column("screen_name")
  val screenName: String,
  @Column("created_at")
  val createdAt: Date
) extends KeyedEntity[Long] {
  lazy val status = Timeline.usersToStatuses.left(this)
  
  def this() = this(0, "", new Date)
  
  def getStatuses() = 
    Timeline.statuses.where(s => s.userId === id)
}

class Status (
  val id: Long,
  val text: String,
  @Column("created_at")
  val createdAt: Date,
  @Column("user_id")
  val userId: Long
) extends KeyedEntity[Long] {
  lazy val user = Timeline.usersToStatuses.right(this)
  
  def this() = this(0, "", new Date, 0)
  
  def getUser() = 
    Timeline.statuses.where(s => s.userId === userId).single
}

object Timeline extends org.squeryl.Schema {
  val users = table[User]("users")
  val statuses = table[Status]("statuses")
  
  val usersToStatuses = 
    oneToManyRelation(users,statuses)
      .via((u,s) => u.id === s.userId)
  
  on(users)(u => declare(
    u.id is (primaryKey) // cancel "autoincremented"
  ))
  on(statuses)(u => declare(
    u.id is (primaryKey) // cancel "autoincremented"
  ))
}

modelsSpec.scala

package com.example.models

import org.specs2._
import org.specs2.mutable._
import java.util.Date

class ModelsSpec extends mutable.Specification {
  import java.sql.{DriverManager,SQLException}
  import org.squeryl.Session
  import org.squeryl.SessionFactory
  import org.squeryl.adapters._
  
  val mysql = Class.forName("com.mysql.jdbc.Driver")
    SessionFactory.concreteFactory = Some(() => Session.create(
      java.sql.DriverManager.getConnection("jdbc:mysql://localhost:3306/myspray","root", "")
      //, new MySQLAdapter)
      , new MySQLInnoDBAdapter)
    )
  
  
  "Timeline" should {
    "be all ok." in {
      import org.squeryl.PrimitiveTypeMode._
      transaction {
        Timeline.drop
        
        Timeline.printDdl
        Timeline.create
        
        // insert
        val newUser = new User(1, "hoge", new Date)
        val inserted = Timeline.users.insert(newUser)
        inserted must beLike {
          case u:com.example.models.User 
              if u.id == 1 && u.screenName == "hoge" => ok
          case _ => ko
        }
        
        // select by inserted value
        Timeline.users.where( u => u.id.===(inserted.id))
          .single must beLike {
            case u:com.example.models.User 
              if u.id == 1 && u.screenName == "hoge" => ok
            case _ => ko
        }
      
        Timeline.statuses.insert(new Status(1, "hi, there.", new Date, inserted.id))
      
        val st = Timeline.statuses.where(s => s.id === 1 and s.userId === inserted.id).single
        st.text .mustEqual(
            inserted.getStatuses().single.text)
      
        2 to 100 foreach{ i => 
          Timeline.statuses.insert(
            new Status(i, "my number is " + i.toString, new Date, 1)) }
      
        // count records
        val user1StatusCount = from(Timeline.statuses)( s => 
          where(s.userId === 1)
          groupBy(s.userId)
          compute(count)).single

        user1StatusCount.key mustEqual 1
        user1StatusCount.measures mustEqual 100

        // paging
        val limitation:Int = 15
        val totalPages:Int = (user1StatusCount.measures / limitation).toInt + 1
        val statusList = from(Timeline.statuses)( s => 
          where(s.userId === 1)
          select (s))
        (0 to totalPages) foreach { pageNo =>
          val pageList = statusList.page( limitation * pageNo, limitation)
          pageNo must be_<=(totalPages)
          pageList.map(_=>1).sum must be_<=(limitation)
        }
        
        // Insertion with unexist user_id
        try {
          Timeline.statuses.insert(new Status(2000, "hi, where?", new Date, 2))
        } catch {
          case ex:java.lang.RuntimeException => 
            println(ex.getMessage)
            ok
          case _ => ko
        }
        
        // delete all data with order
        Timeline.statuses.deleteWhere(s => s.userId === inserted.id)
        Timeline.users.deleteWhere(u => u.id === inserted.id)
        
      } // transaction
      ok
    }
  }
}

Project.scala

import sbt._
import Process._

class Project(info: ProjectInfo) extends DefaultProject(info) {
  val specs2 = "org.specs2" %% "specs2" % "1.5" % "test"
  val squeryl = "org.squeryl" %% "squeryl" % "0.9.4"
  val mysqlDriver = "mysql" % "mysql-connector-java" % "5.1.17"
}