Scalaz（37）－ Free ：实践－DB Transaction free style

  我一直在不断的提示大家：FP就是Monadic Programming，是一种特殊的编程风格。在我们熟悉的数据库编程领域能不能实现FP风格呢？我们先设计一些示范例子来分析一下惯用的数据库编程过程：

import scalaz._
import Scalaz._
import scala.language.higherKinds
import scala.language.implicitConversions
import com.jolbox.bonecp.BoneCP
import com.jolbox.bonecp.BoneCPConfig
import java.sql.Connection
import java.sql.ResultSet

object freedbtxns {
def getTutorId(courseId: Int, conn: Connection): Int = {
  val sqlString = "select TUTOR from COURSES where ID=" + courseId
  conn.createStatement().executeQuery(sqlString).getInt("ID")
}  
def getTutorPay(courseId: Int, conn: Connection): Double = {
  val sqlString = "select PAYAMT from COURSES where ID=" + courseId
  conn.createStatement().executeQuery(sqlString).getDouble("PAYAMT")
}  
def getStudentFee(courseId: Int, conn: Connection): Double = {
  val sqlString = "select FEEAMT from COURSES where ID=" + courseId
  conn.createStatement().executeQuery(sqlString).getDouble("FEEAMT")
}  
def updateTutorPay(tutorId: Int, plusAmt: Double, conn: Connection): Unit = {
  val sqlString = "update TUTORS set PAYABLE = PAYABLE+"+plusAmt.toString + " where ID=" + tutorId
  conn.createStatement().executeUpdate(sqlString)
}  
def updateStudentFee(studentId: Int, plusAmt: Double, conn: Connection): Unit = {
  val sqlString = "update STUDENTS set DUEAMT = DUEAMT+"+plusAmt.toString + " where ID=" + studentId
  conn.createStatement().executeUpdate(sqlString)
}  
def findEmptySeat(courseId: Int, conn: Connection): Int = {
  val sqlString = "select ID from SEATS where OCCUPIED='T' AND ID=" + courseId
  conn.createStatement().executeQuery(sqlString).getInt("ID")
}
def updateSeatsStatus(seatId: Int, taken: Boolean, conn: Connection): Unit = {
  val sqlString = "update SEATS set OCCUPIED ='"+taken.toString.toUpperCase.head + "' where ID=" + seatId
  conn.createStatement().executeUpdate(sqlString)
}  

我这里模拟了一个培训学校内的一些业务。上面设计的是一些基本函数，可以分别对学员、导师、座位进行查询和更新。如果我们需要把更新工作放入事务处理（transaction）内的话我们可以这样做：

def updateStudent(studentId: Int, courseId: Int): Unit = {
   val config = new BoneCPConfig()
   val bonecp = new BoneCP(config)
   val conn = bonecp.getConnection()
   conn.setReadOnly(false)
   conn.setAutoCommit(false)
   conn.rollback()
   try {
     val fee = getStudentFee(courseId, conn)
     updateStudentFee(studentId,fee, conn)
     conn.commit()
   } catch {
     case (e:Exception) => conn.rollback()
   } finally {
     conn.close()
   }   
}
def updateStudentAndSeat(studentId: Int, courseId: Int): Unit = {
   val config = new BoneCPConfig()
   val bonecp = new BoneCP(config)
   val conn = bonecp.getConnection()
   conn.setReadOnly(false)
   conn.setAutoCommit(false)
   conn.rollback()
   try {
     val fee = getStudentFee(courseId, conn)
     updateStudentFee(studentId,fee, conn)
     val seatId = findEmptySeat(courseId, conn)
     updateSeatsStatus(seatId, true, conn)
     conn.commit()
   } catch {
     case (e:Exception) => conn.rollback()
   } finally {
     conn.close()
   }   
}

马上可以发现在我们对这些函数在事务处理内进行组合使用时我们必须重新对事务处理进行设置，无法实现真正意义上的函数组合。如果我们认可FP风格的话，这里起码有两项弊处：一是源代码增加了大量的铺垫（boilerplate code）,重复事务处理设置、二是每个更新函数都会产生副作用，换句话说就是这里那里都会有副作用影响，很难控制，这样就增加了程序的复杂程度，造成代码分析的困难。

我们希望达到的目标：

/*
def updateStudentAndSeat(studentId: Int): program {
 // findEmptySeat
 // updateStudentFee
 // updateSeatStatus 
}

def runDBTxn(prg: program) {
  //conn＝ getConnection
  //try
  // run(pre)
  //commit
  //catch
  //rollback
}
runDBTxn(updateStudent)
runDBTxn(updateStudentAndSeat)
runDBTxn(updateSeatStatus)
*/  

我们只在一个地方设置和运行事务处理。我们希望能把不同的program传入runDBTxn去运算。这不就是Free Monad的编程、运算关注分离模式嘛。那我们就试着用Free Monad来提供数据库事务处理支持。按上篇讨论的设计流程我们先设计ADT：

case class SqlOp[A](run: Connection => A)

模拟sql指令很简单，两种情况：query或者update。两者都可以用函数run表示：传入Connection,返回结果A，A有可能是Unit。要成为Free Monad就必须先获取SqlOp的Functor实例：

case class SqlOp[A](run: Connection => A)
implicit val sqlOpFunctor = new Functor[SqlOp] {
  def map[A,B](sa: SqlOp[A])(f: A => B): SqlOp[B] = 
    SqlOp{ (conn: Connection) => f(sa.run(conn)) }
}

基本功能的sql操作函数及升格Free：

type Sql[A] = Free[SqlOp,A]
def getTutorId(courseId: Int): Sql[Int] = 
  Free.liftF(SqlOp{
    (conn: Connection) => {
      val sqlString = "select TUTOR from COURSES where ID=" + courseId
      conn.createStatement().executeQuery(sqlString).getInt("ID")
    }  
  })
  
def getTutorPay(courseId: Int): Sql[Double] = 
  Free.liftF(SqlOp{
    (conn: Connection) => {
      val sqlString = "select PAYAMT from COURSES where ID=" + courseId
      conn.createStatement().executeQuery(sqlString).getDouble("PAYAMT")
    }  
  })
def getStudentFee(courseId: Int): Sql[Double] = 
  Free.liftF(SqlOp{
    (conn: Connection) => {
     val sqlString = "select FEEAMT from COURSES where ID=" + courseId
     conn.createStatement().executeQuery(sqlString).getDouble("FEEAMT")
    }  
  })
def updateTutorPay(tutorId: Int, plusAmt: Double): Sql[Unit] = 
  Free.liftF(SqlOp{
    (conn: Connection) => {
      val sqlString = "update TUTORS set PAYABLE = PAYABLE+"+plusAmt.toString + " where ID=" + tutorId
      conn.createStatement().executeUpdate(sqlString)
    }  
  })
def updateStudentFee(studentId: Int, plusAmt: Double): Sql[Unit] = 
  Free.liftF(SqlOp{
    (conn: Connection) => {
      val sqlString = "update STUDENTS set DUEAMT = DUEAMT+"+plusAmt.toString + " where ID=" + studentId
      conn.createStatement().executeUpdate(sqlString)
    }  
  })
def findEmptySeat(courseId: Int): Sql[Int] = 
  Free.liftF(SqlOp{
    (conn: Connection) => {
      val sqlString = "select ID from SEATS where OCCUPIED='T' AND ID=" + courseId
      conn.createStatement().executeQuery(sqlString).getInt("ID")
    }  
  })
def updateSeatsStatus(seatId: Int, taken: Boolean): Sql[Unit] = 
  Free.liftF(SqlOp{
    (conn: Connection) => {
      val sqlString = "update SEATS set OCCUPIED ='"+taken.toString.toUpperCase.head + "' where ID=" + seatId
      conn.createStatement().executeUpdate(sqlString)
    }  
  })

我们现在可以用这些升格成Free的函数来建设AST示范例子：

  def takeSeat(courseId: Int): Sql[Unit] = for {
    emptySeat <- findEmptySeat(courseId)
    _ <- updateSeatsStatus(emptySeat, true)
  } yield()
  def addCourse(studentId: Int, courseId: Int): Sql[Unit] = for {
    fee <- getStudentFee(courseId)
    pay <- getTutorPay(courseId)
    tutorId <- getTutorId(courseId)
    _ <- updateStudentFee(studentId, fee)
    _ <- updateTutorPay(tutorId, pay)
    _ <- takeSeat(courseId)
  } yield()

addCourse对基本函数进行了组合，又调用了已经组合过一次的takeSeat，证明AST可以实现高度的函数组合。

下面示范实现相关的Interpreter：

  def runTransactionImpl[A](conn: Connection, ast: Sql[A]): A = 
    ast.resume.fold ({
      case x: SqlOp[Sql[A]] => runTransactionImpl(conn, x.run(conn))
    },
    (a: A) => a 
   )

我们需要一个通用的事务处理方法：

  def runTransaction[A](ast: Sql[A]): Exception \/ A = {
    val config = new BoneCPConfig()
    val bonecp = new BoneCP(config)
    val conn = bonecp.getConnection()
    conn.setReadOnly(false)
    conn.setAutoCommit(false)
    conn.rollback()
    try {
      val result: A = runTransactionImpl(conn, ast)
      result.right[Exception]
    } catch {
      case e: Exception => e.left[A]
    } finally {
      conn.close
    } 
  }

这样，我们可以在一个地方使用事务处理来运算任何事先设计的AST。

我们可以用不同的方法来实现Interpreter。下面就是用Free.foldMap来运算AST的示范。由于我们需要注入Connection，所以采用了Sql to State的自然转换（natural transformation）：

  type SqlState[A] = State[Connection, A]
  object SqlToState extends (SqlOp ~> SqlState) {
    def apply[A](sa: SqlOp[A]): SqlState[A] = sa match {
      case SqlOp(f) => State {
        conn => (conn,f(conn))
      }
    }
  }
  def runTransactionImplState[A](conn: Connection, ast: Sql[A]) =
    ast.foldMap(SqlToState).run(conn)

下面是这个用Free来实现FP风格数据库事务处理的完整示范代码：

import scalaz._
import Scalaz._
import scala.language.higherKinds
import scala.language.implicitConversions
import com.jolbox.bonecp.BoneCP
import com.jolbox.bonecp.BoneCPConfig
import java.sql.Connection
import java.sql.ResultSet

object freedbtxns {

case class SqlOp[A](run: Connection => A)
implicit val sqlOpFunctor = new Functor[SqlOp] {
  def map[A,B](sa: SqlOp[A])(f: A => B): SqlOp[B] = 
    SqlOp{ (conn: Connection) => f(sa.run(conn)) }
}
type Sql[A] = Free[SqlOp,A]
def getTutorId(courseId: Int): Sql[Int] = 
  Free.liftF(SqlOp{
    (conn: Connection) => {
      val sqlString = "select TUTOR from COURSES where ID=" + courseId
      conn.createStatement().executeQuery(sqlString).getInt("ID")
    }  
  })
  
def getTutorPay(courseId: Int): Sql[Double] = 
  Free.liftF(SqlOp{
    (conn: Connection) => {
      val sqlString = "select PAYAMT from COURSES where ID=" + courseId
      conn.createStatement().executeQuery(sqlString).getDouble("PAYAMT")
    }  
  })
def getStudentFee(courseId: Int): Sql[Double] = 
  Free.liftF(SqlOp{
    (conn: Connection) => {
     val sqlString = "select FEEAMT from COURSES where ID=" + courseId
     conn.createStatement().executeQuery(sqlString).getDouble("FEEAMT")
    }  
  })
def updateTutorPay(tutorId: Int, plusAmt: Double): Sql[Unit] = 
  Free.liftF(SqlOp{
    (conn: Connection) => {
      val sqlString = "update TUTORS set PAYABLE = PAYABLE+"+plusAmt.toString + " where ID=" + tutorId
      conn.createStatement().executeUpdate(sqlString)
    }  
  })
def updateStudentFee(studentId: Int, plusAmt: Double): Sql[Unit] = 
  Free.liftF(SqlOp{
    (conn: Connection) => {
      val sqlString = "update STUDENTS set DUEAMT = DUEAMT+"+plusAmt.toString + " where ID=" + studentId
      conn.createStatement().executeUpdate(sqlString)
    }  
  })
def findEmptySeat(courseId: Int): Sql[Int] = 
  Free.liftF(SqlOp{
    (conn: Connection) => {
      val sqlString = "select ID from SEATS where OCCUPIED='T' AND ID=" + courseId
      conn.createStatement().executeQuery(sqlString).getInt("ID")
    }  
  })
def updateSeatsStatus(seatId: Int, taken: Boolean): Sql[Unit] = 
  Free.liftF(SqlOp{
    (conn: Connection) => {
      val sqlString = "update SEATS set OCCUPIED ='"+taken.toString.toUpperCase.head + "' where ID=" + seatId
      conn.createStatement().executeUpdate(sqlString)
    }  
  })

  def takeSeat(courseId: Int): Sql[Unit] = for {
    emptySeat <- findEmptySeat(courseId)
    _ <- updateSeatsStatus(emptySeat, true)
  } yield()
  def addCourse(studentId: Int, courseId: Int): Sql[Unit] = for {
    fee <- getStudentFee(courseId)
    pay <- getTutorPay(courseId)
    tutorId <- getTutorId(courseId)
    _ <- updateStudentFee(studentId, fee)
    _ <- updateTutorPay(tutorId, pay)
    _ <- takeSeat(courseId)
  } yield()

  def runTransactionImpl[A](conn: Connection, ast: Sql[A]): A = 
    ast.resume.fold ({
      case x: SqlOp[Sql[A]] => runTransactionImpl(conn, x.run(conn))
    },
    (a: A) => a 
   )
  def runTransaction[A](ast: Sql[A]): Exception \/ A = {
    val config = new BoneCPConfig()
    val bonecp = new BoneCP(config)
    val conn = bonecp.getConnection()
    conn.setReadOnly(false)
    conn.setAutoCommit(false)
    conn.rollback()
    try {
      val result: A = runTransactionImpl(conn, ast)
      result.right[Exception]
    } catch {
      case e: Exception => e.left[A]
    } finally {
      conn.close
    } 
  }
}