Wednesday, October 5, 2011

Scalaquery Example

This is an example of scalaquery. Some save, update and find queries can be found at the end of the post. First of all the table structure should be created in scalaquery to create the table as desired in whatever the database.
Let's have a look at the model of the table. This table does not consist of any primary or foreign key constraints. The object, MoMessageHistory will map to a table by the name "mo_message_history" in the database.

==================================================================

object MoMessageHistory extends Table[(String, String, String, Long, String)]("mo_message_history") {
  def correlationId = column[String]("correlation_id", O NotNull, O DBType "VARCHAR(60)")
  def text = column[String]("text", O NotNull)
  def timestamp = column[Long]("time_stamp", O NotNull, O DBType "BIGINT(15)")
  def validity = column[String]("validity", O NotNull, O DBType "VARCHAR(20)")
  def msisdn = column[String]("msisdn", O NotNull, O DBType "VARCHAR(15)")
  def * = correlationId ~ msisdn ~ text ~ timestamp ~ validity
}

===================================================================

The object, MoMessageHistory extends Table which generalizes with scala data types (the data types within square brackets and then parentheses in the first line). These data types are those of attributes of MoMessageHistory. "def * = correlationId ~ msisdn ~ text ~ timestamp ~ validity" projects the columns so that they'll be available for the queries. The number of columns projected and the data types of the same should exactly match the data types of the first line and should also be in the same order.

Now let's explore the line,
def correlationId = column[String]("correlation_id", O NotNull, O DBType "VARCHAR(60)")
Here the attribute of MoMessageHistory, correlationId which is of type String of scala will map to the column, "correlation_id" in the database table mo_message_history. The column, correlation_id can not be null and is of the database data type, varchar(60).

Below is a save query. The method insert is called on MoMessageHistory. The data to be saved should be given in the order of the projection (as discussed above) at the table creation.

===================================================================

def save(correlationId:String,msisdn: String, text: String, validity: String) = {
        MoMessageHistory insert (correlationId, msisdn, text,     System.currentTimeMillis().asInstanceOf[Long], validity)
}

===================================================================

The following code returns a list of records from the database by msisdn, a field of MoMessasgeHistory. Here the list returned is made of tuples (List[(String, String, String, Long, String)]). 
"MoMessageHistory where {_.msisdn === msisdn}" generates a scalaquery 'Query'. the 'list' method called on it will return the desired results.
===================================================================

def find(msisdn: String): List[(String, String, String, Long, String)] = {
   val q = MoMessageHistory where {_.msisdn === msisdn}
    println("find query: \n" + q.selectStatement)
   q.list
}

===================================================================

This query also uses a where clause like the above, except for the fact it has 3 selection criteria.  Attributes of the MoMessageHistory are referred using _.
===================================================================

def find(msisdn: String, from: java.util.Date, to: java.util.Date): List[(String, String, String, Long, String)] = {
   val q = MoMessageHistory where {_.msisdn === msisdn} where {_.timestamp <= to.getTime} where  {_.timestamp >= from.getTime}
   println("find query: \n" + q.selectStatement)
  q.list
}

===================================================================

The query below updates the validity field of tuples with a siven id. First a query is generated to select the effecting records. Then updating is done on the same.
===================================================================

def updateValidity(id:String , validity:String)={
  val q1 = for (msg <- MoMessageHistory if msg.correlationId === id)
  yield msg.validity

  if (!q1.list.isEmpty) {
    println("found item " + id +validity)
    q1 update (validity)
    printf("updating list %s", q1.list)

  }

===================================================================

The query,
'val q1 = for (msg <- MoMessageHistory if msg.correlationId === id)
yield msg.validity'
is similar to the sql query, 
'select msg.validity from mo_message_history where msg.correlationId=<the id>'

If the query yields any results, It is updated with the validity parameter passed into the method.
'q1 update (validity)'


===================================================================


thanks,
Shyarmal