Archive for January, 2010

scala dsl to perform sql searches: part 1

Reading designing-internal-dsls-in-scala.html(recommended!)
I come up with the idea of doing some simple scala DSL,
to write and perform SQL searchs using lift-mapper objects.

The result would be something like that:


var find = FIND(Item)
    WITH(ItemType.name AS type_name, ItemResponsible.name as responsible)
    WHEN (
        "type_name" ILIKE _nameVariable,
        "cancelled" IS_NULL,
        OR (
            "responsible" IS_NULL,
            "ItemResponsible.id" EQUALS _getLoggedUserID()
        )
    )
    LIMIT 100 OFFSET 20

...
performSearch(find)

Here’s a sample code



// types
type Condition = (() => Boolean, () => String)
type Operator  = (String, List[Condition])    

def _mergeConditions(operator: Operator): String = {
    var shouldApplyAndOperator = false // ????      

    var sql = ""
    var operatorFilters = ""
    val (operatorSQL, conditions) = operator

    var shouldApplyOperatorFilters = false

    conditions.foreach(cdt => {
        val (condition, filter) = cdt

        if (condition()) {
            val filterValue = shouldApplyOperatorFilters match {
                case true  => " %s (%s) ".format(operatorSQL, filter())
                case _ => " %s ".format(filter())                      
            }                                                          

            operatorFilters += filterValue
            shouldApplyOperatorFilters = true
        }                                    
    })                                       

    if (shouldApplyOperatorFilters) {
        val filterValue = shouldApplyAndOperator match {                        
            case true  => " %s (%s) ".format("AND", operatorFilters)         
  
            case _ => " %s ".format(operatorFilters)                         
  
        }                                                                       
                                                             
        sql += filterValue                                                      
        shouldApplyAndOperator = true                                           
    }                                                                           
                                                             
    sql                                                                         
}                                                                               
                                                             

// Base class to search objects
class Find(obj: String) {      
    private var fields: List[Any]         = List()
    private var operators: List[Operator] = List()

    def toSQL: String = {
        def _getFieldsAsSQL = this.fields.reduceLeft(_ + "," + _)
        def _getTableName = this.obj                             
        def _getOperatorsAsSQL = {                               
            var sql = ""                                         

            this.operators.foreach(op => {
                sql += _mergeConditions(op)
            })                             

            if (sql != "") sql = " WHERE " + sql
            sql                                 
        }                                       

        "SELECT %s FROM %s %s".format(
                _getFieldsAsSQL,      
                _getTableName,        
                _getOperatorsAsSQL)   
    }                                 

    /**
     * Adds a list of conditions around a sql AND operator
     */                                                   
    def AND(conditions: Condition*): Find = {             
        this.operators = ("AND", conditions.toList) :: this.operators
        this                                                         
    }                                                                

    /**
     * Adds a list of conditions around a sql OR operator
     */                                                  
    def OR(conditions: Condition*): Find = {             
        this.operators = ("OR",  conditions.toList) :: this.operators
        this                                                         
    }                                                                

    /**
     * Selects the object with custom fields
     */                                     
    def WITH(fields: Any*): Find = {        
        this.fields = fields.toList ::: this.fields
        this                                       
    }                                              
}                                                  

/** Global operator methods to composite conditions around a find object **/
def AND(conditions: Condition*): Condition = {                              
    (() => true, () => _mergeConditions("AND", conditions.toList))        
 
}                                                                           

def OR(conditions: Condition*): Condition = {
    (() => true, () => _mergeConditions("OR", conditions.toList))
}                                                                

// CONDITIONS AND WRAPPERS

class StaticConditionWrapper(filter: String) extends FunctionConditionWrapper(()
=> filter)
class FunctionConditionWrapper(filter: () => String) {                       
 
    def ALWAYS: Condition = {                                                  
        (() => true, filter)                                                
    }                                                                         
    def IF(condition: () => Boolean): Condition = {                        
        (condition, filter)                                                     
    }
    def IF(value: Boolean): Condition = {
        (() => value, filter)
    }
    def ILIKE(value: String): Condition = {
        (() => value != "", () => " %s ILIKE '%%%s%%' ".format(filter(),
value))
    }
    def IS_NULL: Condition = {
        (() => true, () => " %s IS NULL ".format(filter()))
    }
    def NOT_NULL: Condition = {
        (() => true, () => " %s IS NOT NULL ".format(filter()))
    }
}

implicit def IF(filter: String) = new StaticConditionWrapper(filter)
implicit def IF(filter: () => String) = new FunctionConditionWrapper(filter)


// Object FIND to easy create find objects (FIND("object_name"))
object FIND {
    def apply(obj: String): Find = new Find(obj)
}


The result of evaluting


// TEST
def checkCondition: () =>> Boolean = () => true
def someFilter: () => String = () => " something true"

val f = (
    FIND("ObjectName") WITH(1,2,3,4,5)
    AND (
        someFilter ALWAYS,
        "A" IF true,
        "B" IF checkCondition,
        OR (
            "C" ALWAYS,
            AND (
                "NAME" ILIKE "Pepe",
                "CANCELLED" IS_NULL
            )
        )
    )
)

println(f.toSQL)

is


SELECT 1,2,3,4,5 FROM ObjectName 
    WHERE 
    something true AND (A)  AND (B)  AND
        (  C  OR (   NAME ILIKE '%Pepe%'   AND ( CANCELLED IS NULL )  )  )

really nice 🙂

All filtering is based on

  • Conditions :
    
    type Condition = (() => Boolean, () => String)
    
    sample : (() => {result_of_checking_condition()),
              () => {create_some_sql_filter()})
    

    A couple, containing a function that will decide if the filter applies or not,
    and a function that makes the filter

  • Operators :
    
    type Operator = (String, List[Condition])
    

    Allows to composite conditions and envolves them with a SQL Operator (And/Or)

Advertisements

Logging database changes in lift-mapper

Here is a little trick to log the changed properties
on the lift-mapper objets.

  • First we define a changeLogger trait that extends Mapper

    
    trait ChangeLogger[T <: Mapper[T] ] {
        def checkChanges(obj: T): Unit = {
            if (obj.dirty_?) {
    
                Log.debug("Object " + obj.getSingleton.dbTableName + " has changed")
    
                obj.formFields.foreach(f => {
                    f.dirty_? match {
                        case true => Log.debug(" ---> Property %s was='%s' and now is = '%s'".format(f.name, f.is.toString, f.was.toString))
                    }
                })
            }
        }
    }
    
    

  • Extend LongKeyedMetaMapper (or any other MetaMapper class) to call ChangeLogger checkChanges method after save (or after delete …)

    
    trait ChangeLoggerMetaMapper[T <: LongKeyedMapper[T]] extends LongKeyedMetaMapper[T]
                                                    with ChangeLogger[T] { self : T =>
        override def afterSave: List[(T) => Unit] = checkChanges _ :: super.afterSave
    }
    
    
    
  • Model object

    
    class Item extends LongKeyedMapper[Item] with IdPK
                                             with ChangeLogger[Item] {
        def getSingleton = Item
    
        object name extends MappedString(this, 100) {
            override def validations = valMinLen(1, "Must be not empty") _ ::
                                       valUnique("Name must be unique") _ ::
                                       super.validations
        }
    }
    
    object Item extends Item with ChangeLoggerMetaMapper[Item] {
        def findByName(name: String): List[Item] = {
            Item.findAll(By(Item.name, name))
        }
    }
    
    
    



The code can be found on http://github.com/jgoday/sample_lift_testing

Documentation for lift-mapper (version lift-1.1-M6) http://scala-tools.org/mvnsites/liftweb-1.1-M6/lift-mapper/scaladocs/index.html