GRAILS Liquibase migration - Execute / run a sql file

The sqlFile method can be used to run a sql file directly from liquibase. Below is the DSL on how this can be done using liquibase and GRAILS.

databaseChangeLog = {

    changeSet(author: "myname", id: "1234567-1") {
        sqlFile( path: 'path-to-sql-file.sql')
    }

}

Make sure the app has permissions to read the file.

GRAILS Liquibase migration - Add / Create new table

Below is the DSL which can be used to create a table using GRAILS and liquibase. The preconditions can be used to check if the table already exists, if not then a new table is created.

databaseChangeLog = {

    changeSet(author: "myname", id: "123456-3") {
        preConditions(onFail: "MARK_RAN", onFailMessage: "table already exists") {
            not {
                tableExists(tableName: "TABLE_NAME")
            }
        }
        createTable(tableName: "TABLE_NAME") {
            column(name: "id", type: "number(19,0)") {
                constraints(nullable: "false", primaryKey: "true", primaryKeyName: "ABC_PK")
            }
            column(name: "COLUMN1", type: "varchar2(255)") {
                constraints(nullable: "true")
            }
            column(name: "COLUMN2", type: "number(19,0)") {
                constraints(nullable: "true")
            }
            column(name: "COLUMN3", type: "timestamp") {
                constraints(nullable: "false")
            }
        }
    }

}

GRAILS Liquibase migration - Add new column

Below code shows how to add a new column in an already existing table using GRAILS and liquibase

databaseChangeLog = {

 changeSet(author: "myname", id: "123456-1") {
  addColumn(tableName: "TABLE_NAME") {
   column(name: "NEW_DATE_COLUMN", type: "TIMESTAMP(6)",defaultValueDate: "2013-01-01") {
    constraints(nullable: "false")
   }
  }                
 }
 changeSet(author: "myname", id: "123456-2") {
  addColumn(tableName: "TABLE_NAME") {
   column(name: "COLUMN_NAME", type: "VARCHAR2(1020 BYTE)")
  }
 }
}

GRAILS Liquibase migration - add unique constraint

Below snippet of groovy code shows how to add a unique constraint using GRAILS and liquibase.

databaseChangeLog = {
    changeSet(author: "myname", id: "1234567890") {
  addUniqueConstraint(columnNames: "COL_NAME", constraintName: "SYS_C00123", deferrable: "false", disabled: "false", initiallyDeferred: "false", tableName: "TABLE_NAME")
 }
}

GRAILS GPARS parallel thread execution ( Threading )

Executing parallel threads in GRAILS using GPARS is pretty straight forward. We can define a closure which we want to execute in a method and then this closure can be executed in different threads. This is the simplest way of achieving parallelism with GRAILS and GPARS.

public static int THREAD_POOL_SIZE = 6

Closure executeMeInParallel = {
 //Your execution code goes here....
 5.times {
   println it
 }
}

def f1 = null; def f2 = null; 
GParsExecutorsPool.withPool(THREAD_POOL_SIZE) {
    f1 = executeMeInParallel.callAsync()
    f2 = executeMeInParallel.callAsync()                
}
f1.get()
f2.get()

That's All !

GRAILS / JAVA MongoDB aggregation year / month / day / hour

Let us try and do some aggregation based on dates. Well, the most common type of aggregation which we can think of using dates is the aggregation by year / date / month etc. etc. I will be doing an aggregation using the morphia library. I will also be using map / reduce to do my aggregation. Let's assume that we have a collection(posts) which has some data like below.

{
        "_id" : ObjectId("52236140e40247b854000002"),
        "author" : "6mRlPExfM03UQTvMDUkS",
        "body" : "uygu4ndeV0",
        "comments" : [
                {
                        "author" : "lalit",
                        "body" : "This is a test"
                }
        ],
        "date" : ISODate("2013-09-01T15:46:08.140Z"),
        "permalink" : "Sbuw5zo5iAuMMAcD008F",
        "tags" : [
                "zeHUw"                
        ],
        "title" : "Sbuw5zo5iAuMMAcD008F"
}

Now, we need to do some aggregation so that we get the counts of all the posts w.r.t year / month / date / hour. Basically, here we would be getting counts for all the post hourly.

We would need to define Map and reduce function to do this and then call the Java Map / reduce API.

Let's get handle to our db and collection.

//injecting the mongo bean to our grails service
def mongo

//service method for map reduce calculation
def mapReduce() {
     DBCollection posts = mongo.db.getCollection("posts")
}

Here goes our map method

private static final String mapHourly = ""
    + "function(){ "
       + "  d = new Date( this.date.getTime() - 18000000 );"
       + "  key = { year: d.getFullYear(), month: d.getMonth(), day: d.getDate(), hour: d.getHours() };"
       + "  emit( key, {count: 1} );"
       + "}";

Here's our reduce method

public static final String reduce = "function(key, values) { " + "var total = 0; "
                                  + "values.forEach(function(v) { " + "total += v['count']; " + "}); " 
                                  + "return {count: total};} ";

Now we will be calling the Map Reduce API.

//setting the commands
MapReduceCommand cmd = new MapReduceCommand(posts, mapHourly,reduce, null, MapReduceCommand.OutputType.INLINE, null);
MapReduceOutput out = posts.mapReduce(cmd);

//printing the results
for (DBObject o : out.results()) {
           System.out.println(o.toString());
}

So, a complete service class might look something like below.

Class MapReduceService {

//getting the bean
def mongo

//map to be used for hourly calculation
final String mapHourly = ""
    + "function(){ "
       + "  d = new Date( this.date.getTime() - 18000000 );"
       + "  key = { year: d.getFullYear(), month: d.getMonth(), day: d.getDate(), hour: d.getHours() };"
       + "  emit( key, {count: 1} );"
       + "}";

//map for daily calculation
private static final String mapDaily = ""
    + "function(){ "
       + "  d = new Date( this.date.getTime() - 18000000 );"
       + "  key = { year: d.getFullYear(), month: d.getMonth(), day: d.getDate(), dow:d.getDay() };"
       + "  emit( key, {count: 1} );"
       + "}";

//map for yearly calculation 
private static final String mapYearly = ""
    + "function(){ "
       + "  d = new Date( this.date.getTime() - 18000000 );"
       + "  key = { year: d.getFullYear() };"
       + "  emit( key, {count: 1} );"
       + "}";

//map for monthly calculation
private static final String mapMonthly = ""
       + "function(){ "
       + "  d = new Date( this.date.getTime() - 18000000 );"
       + "  key = { year: d.getFullYear(), month: d.getMonth() };"
       + "  emit( key, {count: 1} );"
       + "}";


public static final String reduce = "function(key, values) { " + "var total = 0; "
                                  + "values.forEach(function(v) { " + "total += v['count']; " + "}); " 
                                  + "return {count: total};} ";

   //service method for map reduce calculation
   def mapReduce() {
     DBCollection posts = mongo.db.getCollection("posts")

     //setting the commands
     // map method can be changed here to use whichever map you want eg. mapDaily, mapYearly ...
     MapReduceCommand cmd = new MapReduceCommand(posts, mapHourly,reduce, null, MapReduceCommand.OutputType.INLINE, null);
     MapReduceOutput out = posts.mapReduce(cmd);

     //printing the results
     for (DBObject o : out.results()) {
           System.out.println(o.toString());
     } 
   }
}

GRAILS / JAVA MONGODB : Aggregation / Grouping

Running a aggregation or grouping in mongo is not straight forward and the only way is map / reduce. I tried both grails plugins Morphia and mongoDB GORM and after googling my ass off I found that it is not possible to do grouping even with criteria builder offered by the GORM plugin :(. Which means groupProperty('col1') wont work :'( :'(

Now, coming to the implementation part. Let's assume the below SQL query which we want to run in mongo

Select col1,count(*) from table1 where col1 = 'blahblah' group by col1

So, now we need to run this type of query in any of mongo collection using a grails service. I am using grails morphia plugin. You can find the plugin here.

The code is divided into below parts.

First inject the bean into your service class

def mongo

Now, get the instance of the collection you want to do grouping on

DBCollection myColl = mongo.db.getCollection("mycoll")

We will define some conditions basically MAP

DBObject condition = new BasicDBObject();
condition.put("lastUpdated",date)
//In case you want multiple conditions on a single column
condition.put("lastUpdated",new BasicDBObject('$gt',fromDate).append('$lt',toDate))

Lets define our grouping column, basically known as key

DBObject keys = new BasicDBObject();
keys.put('col1',true);
//If you want to add more columns for grouping
keys.put('col2',true);

Setting the initial value of count to zeeroooo

DBObject initial = new BasicDBOject()
initial.put("count",0)

Finally the reduce function where we will do the counting

String reduce = "function(duration,out){ out.count++ }"

That's all.. now we are set to get the results. The return value will be a BSONList

DBObject results = myColl.group(keys,condition,initial,reduce)

Finally putting it all together...

Class MyMongoService {
         
        def mongo

        def myMongoGrouping(params) {
             DBCollection myColl = mongo.db.getCollection("myColl")
             DBObject condition = new BasicDBObject()
             DBObject initial = new BasicDBObject()
             DBObject keys = new BasicDBObject()

             //conditions
             condition.put("lastUpdated",new BasicDBObject('$gt',fromDate).append('$lt',toDate))

             //grouping column
             keys.put('col1',true)
             
             //setting counter to 0
             initial.put("count",0)

             //writing the reduce function
             String reduce = "function(duration,out){ out.count++; }"

             //Here goes our results, a BSONList
             DBObject results = myColl.group(keys,condition,initial,reduce)

        }
}

In case you are using the GORM plugin for GRAILS then you can get the connection with this line

def db = mongo.getDB("myDb")

Rest everything should remain the same. Not tried tough with the GORM plugin.

HAPPY CODING :)
-Lalit